how do i join third table values into main join?-CodePudding

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?

CodePudding user response：

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

SELECT 
   pranks.prank,
   COALESCE(dares.dare, 'No')
FROM pranks LEFT OUTER JOIN 
    dares ON pranks.id = dares.prank_id
ORDER BY pranks.prank ASC;

CodePudding user response：

clearlyclueless gave correct explanations To achieve the result, the following SELECT can also be used:

SELECT 
    pranks.prank,
    case 
    when dare is null then 'No'
    else dare
    end
FROM pranks LEFT  OUTER JOIN 
    dares ON pranks.id = dares.prank_id