CODEWITHSUNDEEP
mysqlleft-join
Homer_J
Homer_J

Reputation: 3323

MySQL Query needs to bring back rows, not empty results

OK, I've voted to delete my earlier question due to stupidity on my part...

I have the following code:

SELECT qnum, id, name, total_staff AS StaffCount, COUNT( q61g ) AS TotalResp, 
(COUNT( q61g ) / total_staff * 100) AS Perc 
FROM tdemog_pfp
LEFT JOIN tresults_pfp ON tdemog_pfp.id = tresults_pfp.q61g
WHERE qnum = 'q61g' AND q60p = '1'
GROUP BY name
ORDER BY name

Now, the first part of this query brings back rows from the tdemog table, for example it will bring back 5 rows of data each row has an id from 1 to 5. What I need the query to do is then bring back data from the tresults table WHERE q60p = 1 for each of the 5 rows brought back in the first part - like a normal `LEFT JOIN'.

Make sense?

H.

Upvotes: 1

Views: 148

Answers (1)

Michael Fredrickson
Michael Fredrickson

Reputation: 37398

Try moving part of your WHERE clause into your JOIN condition:

SELECT ...
FROM tdemog_pfp
LEFT JOIN tresults_pfp ON tdemog_pfp.id = tresults_pfp.q61g AND q60p = '1'
WHERE qnum = 'q61g'
GROUP BY name
ORDER BY name

If you have a field from your second table in your WHERE clause, it will restrict the entire record... but if you put it into your JOIN condition, the record from the first table should still be returned even when the record in the second table doesn't meet the additional criteria...

I'm not sure which column belongs to which table... but move whatever columns are in your second table into your JOIN.

Upvotes: 1

Related Questions