Search code examples
sqlsql-serverleft-joinwindow-functionsfull-outer-join

Join Multiple tables with No Relation

I need to join 3 tables and there are no relations between these tables other than they all have common fields (profiledid and instanceid) For example, these 3 tables are Achievement [0 records], Attachment [6 records] and collegeattended [4 records]

If I use inner join it will perform Cartesian product and I will get 24 records,

SELECT a.*, v.*, c.* FROM dbo.Profile p 
LEFT JOIN Achievement v ON p.id = v.ProfileId AND v.InstanceId = 6559
LEFT JOIN Attachment a ON p.id = a.ProfileId AND a.InstanceId = 6559
LEFT JOIN CollegeAttended c ON p.id = c.ProfileId AND c.InstanceId = 6559
WHERE p.Id = 5574443

however, what I need to get is only 6 records.

I wrote this query, and I got the 6 records. However, this query will just run fine if the driving table (in this query attachment) has the biggest number of row.

SELECT t1.*, t2.*, t3.* 
FROM (SELECT a.*, ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM Attachment a
    WHERE a.ProfileId = 5574443 AND a.InstanceId = 6559) AS t1
LEFT OUTER JOIN (
    SELECT b.*, ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM Achievement b
    WHERE b.ProfileId = 5574443 AND b.InstanceId = 6559) AS t2 ON t1.rn = t2.rn
LEFT OUTER JOIN (
    SELECT c.*, ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM CollegeAttended c
    WHERE c.ProfileId = 5574443 AND c.InstanceId = 6559) AS t3 ON t1.rn = t3.rn

If the attachment has 0 records, this query will not return any records.

Is there a way I can write a query to perform what I need

thanks

Solution

  • You could switch to full joins, if your database supports that feature. It is a bit tricky if your database does not support using, but you can do:

    SELECT t1.*, t2.*, t3.* 
FROM (SELECT a.*, ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM Attachment a
    WHERE a.ProfileId = 5574443 AND a.InstanceId = 6559
) AS t1
FULL JOIN (
    SELECT b.*, ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM Achievement b
    WHERE b.ProfileId = 5574443 AND b.InstanceId = 6559
) AS t2 ON t1.rn = t2.rn
FULLL JOIN (
    SELECT c.*, ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM CollegeAttended c
    WHERE c.ProfileId = 5574443 AND c.InstanceId = 6559
) AS t3 ON COALESCE(t1.rn, t2.rn) = t3.rn