Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««45678»»»

T SQL Expand / Collapse
Author
Message
Posted Wednesday, May 27, 2009 10:53 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, December 20, 2012 1:03 PM
Points: 265, Visits: 589
Goes to prove that you get hanged by the same clever rope you wove.
Post #724359
Posted Wednesday, May 27, 2009 10:55 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, April 04, 2014 1:13 PM
Points: 384, Visits: 376
Argh! The question changed from the email and I didn't read the web page's version of the question!
Post #724362
Posted Wednesday, May 27, 2009 10:59 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 12:05 PM
Points: 3,569, Visits: 72,411
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
Post #724367
Posted Wednesday, May 27, 2009 11:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 16, 2010 10:55 AM
Points: 33, 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.
Post #724378
Posted Wednesday, May 27, 2009 11:47 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, June 07, 2011 7:20 AM
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.
Post #724407
Posted Wednesday, May 27, 2009 12:43 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, February 04, 2014 9:35 AM
Points: 1,277, Visits: 1,612
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.



Post #724459
Posted Wednesday, May 27, 2009 1:18 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 7:03 AM
Points: 3,924, Visits: 1,589
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.
Post #724488
Posted Wednesday, May 27, 2009 2:17 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 5:22 PM
Points: 123, Visits: 133

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

Post #724541
Posted Wednesday, May 27, 2009 2:44 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, June 07, 2011 7:20 AM
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!
Post #724576
Posted Wednesday, May 27, 2009 2:48 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, June 07, 2011 7:20 AM
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.
Post #724585
« Prev Topic | Next Topic »

Add to briefcase «««45678»»»

Permissions Expand / Collapse