in query here i have https://www.db-fiddle.com/f/32Kc3QisUEwmSM8EmULpgd/1
SELECT p.prank, d.dare
FROM dares d
INNER JOIN pranks p ON p.id = d.prank_id
WHERE d.condo_id = 1;
i have one condo
with id 1 and it have unique connection to dares
that has connection to pranks and unique connection to condos_pranks
and i wanna have all unique pranks from both tables and i used this query above to get relation of
dares to pranks
and expected result was L,M,N - Yes,No,Maybe
and it is correct but i also wanna have those in condos_pranks
which ids are 1,4,5,6 = L,O,P,Q
so i tried to join the table with left join because it might not have condos_pranks
row
SELECT p.prank, d.dare
FROM dares d
INNER JOIN pranks p ON p.id = d.prank_id
LEFT JOIN condos_pranks pd ON pd.condo_id = d.condo_id AND pd.prank_id = p.id
WHERE d.condo_id = 1;
but result is same as first and what i want is
prank | dare |
---|---|
L | Yes |
M | No |
N | Maybe |
O | No |
P | No |
Q | No |
with default being No = 2
if prank_id of condos_pranks
is not in dares
how to connect it?
2
Answers
This seems like an exercise in identifying extraneous information more than anything. You are unable to join something to a table that has no key, however if you know your default then you may use something like coalesce to identify the records where there was no data to join
NULL
and replace them with your default.I mentioned in a comment above that this table schema makes little sense. You have keys all over the place that doing have all sorts of circular references. If this is your derived schema, consider stopping here and revisiting the relationships. If it is not and it is something educational, which I suspect it is, disregard and recognize the logical flaws in what you are working in. Perhaps consider taking the data provided and creating a new table schema that is more normalized and uses other tables to handle the many to many and one to many relationships.
dbfiddle
clearlyclueless gave correct explanations
To achieve the result, the following SELECT can also be used: