Post reply

Sql Server Joins

  • 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

  • March 16, 2009 at 12:00 am

    #960049

    Hope this helps..

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • June 17, 2010 at 3:22 pm

    #1181861

    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.

  • June 18, 2010 at 5:38 am

    #1182040

    SELECT

    T1.col1,

    T1.col2,

    T2.col3,

    T2.col4

    FROM

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

  • June 18, 2010 at 5:42 am

    #1182045

    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

  • June 18, 2010 at 5:48 am

    #1182047

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

  • June 18, 2010 at 6:34 am

    #1182078

    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

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

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