SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Join Predicate


Join Predicate

Author
Message
Mauve
Mauve
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3456 Visits: 2066
The code shows a clear cut example of why columns in a query should always be qualified. I.e., ALWAYS use aliases!

I got burnt badly with a DELETE statement in a trigger that was using an unqualified reference to the "DELETED" table with the end result being that more rows than what was intended was being deleted.

Using an alias on the table and then qualifing the column will shake out any coding errors, typos, etc. during the parsing phase of the query execution.

Example delete trigger:
DELETE FROM Foo
WHERE FooBar IN (SELECT d.FooBar FROM deleted d)


(PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.
Tom Garth
Tom Garth
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2047 Visits: 1499
I don't know about the unqualified reference, but I do know that the 'deleted' temp table frequently contains rows that are NOT really being deleted, like during updates.

Why wouldn't you have used a foreign key with cascaded deletes instead?

Tom

Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

StarNamer
StarNamer
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2812 Visits: 1992
Tejinder Arora (12/13/2007)
The answer you have posted is wrong
I tried it on SQL server and it says
"Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'MIDDLE'."

The only way to get this error is to put an alias on the Customer table. If you use the query as written, 'MIDDLE' is taken as the alias name and no error occurs.

Derek
Mauve
Mauve
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3456 Visits: 2066
tgarth (12/14/2007)

Why wouldn't you have used a foreign key with cascaded deletes instead?


I generally do. Triggers are only an exception to the rule. There are certain times when a trigger is necessary due to the application and/or the data model.

Examples:
- SQL Server 2000 doesn't support ON DELETE SET NULL
- Often times in a hierarchical structure (i.e., parent -> child relationships) you'll get burnt as the FK constraint is checked at each row deletion. SQL Server doesn't support "deferrable" (check at the end of the transaction) constraint checking like Oracle does.


(PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.
HardCoder
HardCoder
Old Hand
Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)Old Hand (357 reputation)

Group: General Forum Members
Points: 357 Visits: 103
Nice twisted question, people had to think once again, i get it wrong although i was knowing that no middle join is there, but do not beyond that it can be alias...

Mohammad Irfan
http://matespoint.blogspot.com
http://www.irfit.com
anuradha-600313
anuradha-600313
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 13
it is working in sql 2005 also...
Have you used the same query OR changed table names?
mayank_sanghvi
mayank_sanghvi
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 55
Nice Question
Iwas Bornready
Iwas Bornready
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22856 Visits: 885
Thanks for the question.
Iwas Bornready
Iwas Bornready
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22856 Visits: 885
I've never used a MIDDLE join.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search