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


T SQL


T SQL

Author
Message
sjsubscribe
sjsubscribe
Old Hand
Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)

Group: General Forum Members
Points: 329 Visits: 595
Goes to prove that you get hanged by the same clever rope you wove.
ppcx
ppcx
SSC-Addicted
SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)

Group: General Forum Members
Points: 443 Visits: 438
Argh! The question changed from the email and I didn't read the web page's version of the question!
mtassin
mtassin
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4850 Visits: 72518
Steve Jones - Editor (5/27/2009)
The corrected question, which is on the web site, is

CREATE TABLE E (eid int, mid int, en char(10) )
INSERT E SELECT 1,2,'Vyas'
INSERT E SELECT 2,3,'Mohan'
INSERT E SELECT 3,NULL,'Shobha'
INSERT E SELECT 4,2,'Shridhar'
INSERT E SELECT 5,2,'Sourabh'

SELECT t1.en [Em], t2.en [Ma] FROM E t1, E t2 WHERE t1.mid = t2.eid
order by t1.en



There is no way you can get "Vyas Mohan" first.

Running the SQL from the newsletter kind of defeats the purpose in answering. The idea is for you to use your knowledge to figure it out.


Sure you can... read it from top down without the ORDER BY.

eid 1 gives you Vyas which joins to eid 2 and gets Mohan
Vyas Mohan
followed by Mohan eid 2 joining to eid 3 and getting
Mohan Shobha

if you read the email, figured out what you were doing, and then clicked the link, and answered the question (because you didn't see the order by.. on my machine it was hidden by a scrollbar at first), you'd easily get Vyas Mohan first.



--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
JustANumber
JustANumber
SSC Journeyman
SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)

Group: General Forum Members
Points: 93 Visits: 113
You're right, my response was somewhat whiny given that the original post was most likely a keyboard error, not a logic error. I'll save my criticisms for when I've accumulated enough points to be classified something beyond Grasshopper.
Barry McConnell
Barry McConnell
SSC-Addicted
SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)

Group: General Forum Members
Points: 432 Visits: 83
I typically read the email, solve the problem in my head, click the link and select the answer I got. Now you're saying I have to ignore the email because what is in there doesn't reflect the actual question? Sounds like a weasel answer to me. Just admit the question was not properly edited in both the content or the alternative presentations and move on.
RML51
RML51
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1457 Visits: 1612
Barry McConnell (5/27/2009)
I typically read the email, solve the problem in my head, click the link and select the answer I got.


That's my MO, too. I don't expect there to be a difference between the newsletter and the website.



SanjayAttray
SanjayAttray
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4421 Visits: 1619
WOW.........never seen so many complaints for a single question on QOD before. Good job Reddy. You made all forum posters stand united.

SQL DBA.
cj_logan
cj_logan
SSC-Enthusiastic
SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)

Group: General Forum Members
Points: 123 Visits: 167
Question

Why is the inner join the recommended option ? I know Microsoft recommends this way but no where can I find a reason ? According to the execution plan both are the same and both cost 50%

SELECT t1.en [Em], t2.en [Ma] FROM E t1, E t2 WHERE t1.mid = t2.eid
order by t1.en

SELECT t1.en [Em], t2.en [Ma] FROM E t1 inner join E t2 on t1.mid = t2.eid
order by t1.en
Barry McConnell
Barry McConnell
SSC-Addicted
SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)

Group: General Forum Members
Points: 432 Visits: 83
SanjayAttray (5/27/2009)
WOW.........never seen so many complaints for a single question on QOD before. Good job Reddy. You made all forum posters stand united.
You must not have seen the MDX Except question then! w00t
Barry McConnell
Barry McConnell
SSC-Addicted
SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)

Group: General Forum Members
Points: 432 Visits: 83
cj_logan (5/27/2009)

Question

Why is the inner join the recommended option ? I know Microsoft recommends this way but no where can I find a reason ? According to the execution plan both are the same and both cost 50%

SELECT t1.en [Em], t2.en [Ma] FROM E t1, E t2 WHERE t1.mid = t2.eid
order by t1.en

SELECT t1.en [Em], t2.en [Ma] FROM E t1 inner join E t2 on t1.mid = t2.eid
order by t1.en

Because that is part of the ANSI standard for SQL. The older syntax should be phased out. The argument goes that the new syntax is cleaner by separating the JOIN logic from the filter (WHERE) logic.
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