Sql Server Joins

Question

Post reply

Sql Server Joins

sabeer

Ten Centuries

Points: 1377
More actions
March 15, 2009 at 11:34 pm

#219159

hi.
I want Self Joins Query with one example table?
:P:P:P:P:P:P:P
Thanks,
Sabeer.M

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Adiga One Orange Chip Points: 27284 More actions · Answer 1

Hope this helps..

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

D Acree SSC Rookie Points: 25 More actions · Answer 2

A great article on this subject by Wagner Crivelini, "SQL & the JOIN Operator".

Article # 67941

Has tables defs and loads for testing the joins.

It really helped me.

carnaud SSC Veteran Points: 266 More actions · Answer 3

SELECT

T1.col1,

T1.col2,

T2.col3,

T2.col4

FROM

[MySelfJoinTableName] T1 JOIN [MySelfJoinTableName] T2 ON T1.FKColName = T2.PKColName

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Adiga (3/16/2009)
Hope this helps..

Yes and no: sample B. Using a self-join to match sales people and their territories isn't a self-join.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

carnaud SSC Veteran Points: 266 More actions · Answer 5

Agreed. I am not sure why Sample B is cited as being a self-join as it does not join to itself! 😛

Irshad Vaza SSC Rookie Points: 46 More actions · Answer 6

Please follow this exmple

CREATE TABLE selftest

(Id char(1),

color varchar(50)

)

GO

INSERT INTO selftest SELECT '1', 'Yellow'

INSERT INTO selftest SELECT '1', 'Red'

INSERT INTO selftest SELECT '2', 'Red'

INSERT INTO selftest SELECT '2', 'Blue'

INSERT INTO selftest SELECT '3', 'White'

INSERT INTO selftest SELECT '3', 'Red'

INSERT INTO selftest SELECT '3', 'Yellow'

Go

--Now to find those Id's which has both Blue and red

colors then Write

:

SELECT t1.id

FROM selftest AS t1 JOIN selftest AS t2

ON t1.color = 'Blue' AND t2.color = 'Yellow'

AND t1.id = t2.id