Join Multiple tables with No Relation

Question

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

(1) Provide sample data and desired results. (2) Tag with the database you are using. (3) The Cartesian product of the tables that you describe would return 0 rows, not 24. — Gordon Linoff, Sep 19 '20 at 12:56
"Relations"/"relationships" in the sense of FKs, like other constraints, are not needed to query. If they happen to hold, certain further expressions return a desired result that otherwise wouldn't. (The actual relations/relationships in the relational model are represented by (base & result) tables & another name for a table is a relation.) — philipxy, Sep 19 '20 at 15:01
Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. [ask] Pause work on the overall goal, chop code to the 1st expression not giving what you expect & say what you expect & why. — philipxy, Sep 19 '20 at 15:03

score 1 · Answer 1 · answered Sep 19 '20 at 12:58

As you describe the problem, you can use full outer join:

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
     ) t1 FULL JOIN
     (SELECT b.*, ROW_NUMBER() OVER (ORDER BY id) AS rn
      FROM Achievement b
      WHERE b.ProfileId = 5574443 AND b.InstanceId = 6559
     ) t2
     USING (rn) FULL JOIN
     (SELECT c.*, ROW_NUMBER() OVER (ORDER BY id) AS rn
      FROM CollegeAttended c
      WHERE c.ProfileId = 5574443 AND c.InstanceId = 6559
     ) t3 
     USING (rn);

Although FULL JOIN and USING are both standard SQL, not all databases support them.

score 1 · Accepted Answer · answered Sep 19 '20 at 13:00

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

Join Multiple tables with No Relation

2 Answers2