none
Subquery in a left join

    Question

  • Hi, I have a long sql query and I have to left join two tables. The problem is that I need to do a subquery in the left join for the second table. Let me give an example.

    • My second table haves this data:

    ID   GAME         CREATED  

    1     SOCCER      1-8-1980

    1     BASKKET     1-9-2000

    2     TENIS           1-10-2000

    2     GOLF           1-12-2000

    2     CARS           1-9-2010

    3     BIKE            1-2-1930

     

     

    My first table left joins the second one by the ID column. So, I want first to determine the max record of each ID of the second table. Ones I have the max record for each ID in table one i want to left join it with table A.

    How do I have to build the subquery in the inner join? I believe is something like this:

     

    SELECT * FROM

    TABLE1

    LEFT OUTER JOIN TABLE2 (SUBQUERY) TABLE2_ALIAS

    ON  TABLE1.ID = TABLE2_ALIAS.ID

     

    Thanks in advance!

     

    Thursday, April 24, 2008 12:03 AM

Answers

  • That's easy.  The answer is already in the code I posted.  Just take the guts of the query I posted out.

     

    Code Snippet

    --get the game of the max created

    select Table2.id,Table2.game,Id_Max.max_created

    from @t2 Table2

    inner join(

    --get the max id for table 2

    select id, max(created) as [Max_Created]

    from @t2

    group by id

    ) Id_Max

    on Table2.id = Id_Max.id and

       Table2.Created = Id_Max.Max_Created

    order by id asc

     

     

     

    Thursday, April 24, 2008 1:25 AM

All replies

  • Try:

     

    ;with cte

    as

    (

    select *, row_number() over(partition by id order created DESC) as rn

    from table2

    )

    select *

    from table1 as a inner join cte as b on a.id = b.id

    where b.rn = 1;

     

    Having an index in table1 by (id, created DESC) include(game), could help to speed the query.

     

     

    AMB

     

    Thursday, April 24, 2008 12:16 AM
  •  

    mm looks complicated Sad

    I believe that my real problem is to find each max record for each ID in table2 .. that is what I have to solve.

    Thursday, April 24, 2008 12:32 AM
  • A more traditional method is to use derived tables, what  you are calling subqueries.

     

    Code Snippet

    declare @t1 table(

    ID int,

    [User] varchar(25)

    )

     

    insert into @t1 values(1,'Adam');

    insert into @t1 values(2,'John');

    insert into @t1 values(3,'Tom');

    insert into @t1 values(4,'Lisa');

     

    declare @t2 table(

    ID int,

    GAME varchar(15),

    CREATED datetime

    )

     

    insert into @t2 values(1,'SOCCER','1-8-1980');

    insert into @t2 values(1,'BASKKET','1-9-2000');

    insert into @t2 values(2,'TENIS','1-10-2000');

    insert into @t2 values(2,'GOLF','1-12-2000');

    insert into @t2 values(2,'CARS','1-9-2010');

    insert into @t2 values(3,'BIKE','1-2-1930');

     

    select Table1.id, Table1.[User], Table2.game, Table2.Max_Created

    from @t1 Table1

    left join(

    --get the game of the max created

    select Table2.id,Table2.game,Id_Max.max_created

    from @t2 Table2

    inner join(

    --get the max id for table 2

    select id, max(created) as [Max_Created]

    from @t2

    group by id

    ) Id_Max

    on Table2.id = Id_Max.id and

       Table2.Created = Id_Max.Max_Created

    ) Table2

    on table1.id = table2.id

     

     

    Thursday, April 24, 2008 12:47 AM
  • Adam, I really apreciate yor reply! Thanks, it was very usefull.

    Now I'm wondering how I can get the ID and the GAME of the max created of the follwing data.

    My result must be:

     

    ID      GAME         CREATED (OPTIONAL)

    1         BASKKET

    2         CARS

    3         BIKE

     

    TABLE

     

    ID   GAME         CREATED  

    1     SOCCER      1-8-1980

    1     BASKKET     1-9-2000

    2     TENIS           1-10-2000

    2     GOLF           1-12-2000

    2     CARS           1-9-2010

    3     BIKE            1-2-1930

     

    Thursday, April 24, 2008 1:05 AM
  • That's easy.  The answer is already in the code I posted.  Just take the guts of the query I posted out.

     

    Code Snippet

    --get the game of the max created

    select Table2.id,Table2.game,Id_Max.max_created

    from @t2 Table2

    inner join(

    --get the max id for table 2

    select id, max(created) as [Max_Created]

    from @t2

    group by id

    ) Id_Max

    on Table2.id = Id_Max.id and

       Table2.Created = Id_Max.Max_Created

    order by id asc

     

     

     

    Thursday, April 24, 2008 1:25 AM
  • Try CTE version.

     

    ;with cte

    as

    (

    select *, row_number() over(partition by ID order by created DESC) as rn

    from @t2

    )

    select a.ID, a.[User], b.ID, b.GAME, b.CREATED

    from @t1 as a inner join cte as b on a.ID = b.ID

    where b.rn = 1;

     

    Result:

     

    ID User ID GAME CREATED
    1 Adam 1 BASKKET 1/9/2000
    2 John 2 CARS 1/9/2010
    3 Tom 3 BIKE 1/2/1930

     

     

    Thanks to Adam for providing the sample data.

     

    AMB

    Thursday, April 24, 2008 1:34 AM