Left Join on Associative Table

Question

I have three tables

Prospect -- holds prospect information

id
name
projectID

Sample data for Prospect

id | name | projectID
1  |  p1  |  1
2  |  p2  |  1
3  |  p3  |  1
4  |  p4  |  2
5  |  p5  |  2
6  |  p6  |  2

Conjoint -- holds conjoint information

id
title
projectID

Sample data

id | title  | projectID
1  | color  |  1
2  | size   |  1
3  | qual   |  1
4  | color  |  2
5  | price  |  2
6  | weight |  2

There is an associative table that holds the conjoint values for the prospects:

ConjointProspect
id
prospectID
conjointID
value

Sample Data

id | prospectID | conjointID | value
1  |      1     |      1     |   20
2  |      1     |      2     |   30
3  |      1     |      3     |   50
4  |      2     |      1     |   10
5  |      2     |      3     |   40

There are one or more prospects and one or more conjoints in their respective tables. A prospect may or may not have a value for each conjoint.

I'd like to have an SQL statement that will extract all conjoint values for each prospect of a given project, displaying NULL where there is no value for a value that is not present in the ConjointProspect table for a given conjoint and prospect.

Something along the lines of this for projectID = 1

prospectID  | conjoint ID  | value
    1       |      1       |   20
    1       |      2       |   30
    1       |      3       |   50
    2       |      1       |   10
    2       |      2       |   NULL
    2       |      3       |   40
    3       |      1       |   NULL
    3       |      2       |   NULL
    3       |      3       |   NULL

I've tried using an inner join on the prospect and conjoint tables and then a left join on the ConjointProspect, but somewhere I'm getting a cartesian products for prospect/conjoint pairs that don't make any sense (to me)

SELECT p.id, p.name, c.id, c.title, cp.value
FROM prospect p
INNER JOIN  conjoint c ON p.projectID = c.projectid
LEFT JOIN conjointProspect cp ON cp.prospectID = p.id
WHERE  p.projectID = 2
ORDER BY p.id, c.id

prospectID  | conjoint ID  | value
    1       |      1       |   20
    1       |      2       |   30
    1       |      3       |   50
    1       |      1       |   20
    1       |      2       |   30
    1       |      3       |   50
    1       |      1       |   20
    1       |      2       |   30
    1       |      3       |   50
    2       |      1       |   10
    2       |      2       |   40
    2       |      1       |   10
    2       |      2       |   40
    2       |      1       |   10
    2       |      2       |   40
    3       |      1       |   NULL
    3       |      2       |   NULL
    3       |      3       |   NULL

Guidance is very much appreciated!!

Answer 1

Your cartesian product is a result of joining by project Id - in your sample data there are 3 prospects with a project id of 1 and 3 conjoints with a project id of 1. Joining based on project id should then result in 9 rows of data, which is what you're getting. It looks like you really need to join via the conjointprospects table as that it what holds the mapping between prospects and conjoint.

What if you try something like:

SELECT p.id, p.name, c.id, c.title, cp.value
FROM prospect p
LEFT JOIN conjointProspect cp ON cp.prospectID = p.id
RIGHT JOIN conjoint c ON cp.conjointID = c.id
WHERE  p.projectID = 2
ORDER BY p.id, c.id

Not sure if that will work, but it seems like conjointprospects needs to be at the center of your join in order to correctly map prospects to conjoints.

Answer 2

Then this will work for you... Prejoin a Cartesian against all prospects and elements within that project via a select as your first FROM table. Then, left join to the conjoinprospect. You can obviously change / eliminate certain columns from result, but at least all is there, in the join you want with exact results you are expecting...

SELECT 
       PJ.*,
       CJP.Value
    FROM 
       (   SELECT 
                   P.ID ProspectID,
                   P.Name,
                   P.ProjectID,
                   CJ.Title,
                   CJ.ID ConJointID
               FROM 
                   Prospect P,
                   ConJoint CJ
               where 
                       P.ProjectID = 1
                   AND P.ProjectID = CJ.ProjectID
               ORDER BY 
                   1, 4
         ) PJ
         LEFT JOIN conjointProspect cjp 
             ON PJ.ProspectID = cjp.prospectID 
            AND PJ.ConjointID = cjp.conjointid
     ORDER BY 
        PJ.ProspectID,
        PJ.ConJointID