The left member of an association on an associative table
I have three tables
Perspective - contains information about perspectives
id
name
projectID
Sample data for a prospectus
id | name | projectID
1 | p1 | 1
2 | p2 | 1
3 | p3 | 1
4 | p4 | 2
5 | p5 | 2
6 | p6 | 2
Consistent - Contains matching 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 containing matching values for perspectives:
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
Their tables have one or more perspectives and one or more matches. Perspective may or may not matter to every collaboration.
I would like to have a SQL statement that will retrieve all matching values for each perspective of a given project, displaying NULL where there is no value for a value that is not in the ConjointProspect table for a given joint and perspective.
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 tried using inner join on perspective and conjugate tables and then left join on ConjointProspect, but somewhere I get Cartesian products for perspective / co-pair 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
The guidance is greatly appreciated!
a source to share
Then this will work for you ... Convert Cartesianism against all perspectives and elements within this project using select as your first FROM table. Then, on the left, join the goal. You can obviously change / exclude certain columns from the result, but at least everything is in the desired connection with the exact results you expect ...
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
a source to share
Your Cartesian product is the result of merging by project ID. In your sample data, there are 3 perspectives with project ID 1 and 3 joins with project ID 1. The connection based on the project ID should then result in the 9 rows of data you get. It looks like you really need to join the merged projections table, as that is what the mapping between perspective and collaborative contains.
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 this will work, but it looks like co-problems should be at the center of your connection in order to display the perspectives for the connections correctly.
a source to share