SQL: 3 self-joins and then join them together

joinsqlsql-serversql-server-2005

I have 2 tables to join in a specific way. I think my query is right, but not sure. 

select t1.userID, t3.Answer Unit, t5.Answer Demo  
FROM    
    table1 t1  
    inner join (select * from table2) t3 ON t1.userID = t3.userID   
    inner join (select * from table2) t5 ON t1.userID = t5.userID  
where  
    NOT EXISTS (SELECT * FROM table1 t2 WHERE t2.userID = t1.userID AND t2.date > t1.date)  
    and NOT EXISTS (SELECT * FROM table2 t4 WHERE t4.userID = t3.userID and t4.counter > t3.counter)  
    and NOT EXISTS (SELECT * FROM table2 t6 WHERE t6.userID = t5.userID and t6.counter > t5.counter)  
    and t1.date_submitted >'1/1/2009'  
    and t3.question = Unit  
    and t5.question = Demo  
order by  
    t1.userID

From table1 I want distinct userID where date > 1/1/2009 

table1      
userID    Date
1         1/2/2009  
1         1/2/2009  
2         1/2/2009  
3         1/2/2009  
4         1/1/2008

So The result I want from table1 should be this: 

userID  
1  
2  
3

I then want to join this on userID with table2, which looks like this: 

table2 
userID    question   answer   counter
1         Unit       A        1
1         Demo       x        1
1         Prod       100      1
2         Unit       B        1
2         Demo       Y        1
3         Prod       100      1
4         Unit       A        1
1         Unit       B        2
1         Demo       x        2
1         Prod       100      2
2         Unit       B        2
2         Demo       Z        2
3         Prod       100      2
4         Unit       A        2

I want to join table1 with table2 with this result:

userID    Unit    Demo
1         B       X
2         B       Z

In other words,
select distinct userID from table2 where question = Unit for the highest counter
and then
select distinct userID from table2 where question = Demo for the highest counter.

I think what I've done is 3 self-joins then joined those 3 together.

Do you think it's right?

Best Solution

SELECT  du.userID, unit.answer, demo.answer
FROM    (
        SELECT  DISTINCT userID
        FROM    table1
        WHERE   date > '1/1/2009'
        ) du
LEFT JOIN
        table2 unit
ON      (userID, question, counter) IN
        (
        SELECT  du.userID, 'Unit', MAX(counter)
        FROM    table2 td
        WHERE   userID = du.userID
                AND question = 'Unit'
        )
LEFT JOIN
        table2 demo
ON      (userID, question, counter) IN
        (
        SELECT  du.userID, 'Demo', MAX(counter)
        FROM    table2 td
        WHERE   userID = du.userID
                AND question = 'Demo'
        )

Having an index on table2 (userID, question, counter) will greatly improve this query.

Since you mentioned SQL Server 2005, the following will be easier and more efficient:

SELECT  du.userID,
        (
        SELECT  TOP 1 answer
        FROM    table2 ti
        WHERE   ti.user = du.userID
                AND ti.question = 'Unit'
        ORDER BY
                counter DESC
        ) AS unit_answer,
        (
        SELECT  TOP 1 answer
        FROM    table2 ti
        WHERE   ti.user = du.userID
                AND ti.question = 'Demo'
        ORDER BY
                counter DESC
        ) AS demo_answer
FROM    (
        SELECT  DISTINCT userID
        WHERE   date > '1/1/2009'
        FROM    table1
        ) du

To aggregate:

SELECT  answer, COUNT(*)
FROM    (
        SELECT  DISTINCT userID
        FROM    table1
        WHERE   date > '1/1/2009'
        ) du
JOIN    table2 t2
ON      t2.userID = du.userID
        AND t2.question = 'Unit'
GROUP BY
        answer
Related Question