CODEWITHSUNDEEP
mysqljoin
Pooshonk
Pooshonk

Reputation: 1324

LEFT JOIN tables mysql, not getting the data correctly

I don't know if this is a problem with my query, or if I am using the wrong thing altogether.

Basically I have 2 tables, submission_data and codes. The codes can be managed in the system, and numbers are submitted against each code in the submissions page. However, if a new code is added after some submissions have been added, the query I use to grab all of the data for a specific submission does not show any new codes added as it is not present in the submission_data table.

SQLFIDDLE

My query is as follows:

SELECT c.code, sd.code_value from submission_data sd 
LEFT JOIN codes c ON c.ID = sd.code_id 
WHERE submission_id = 1

I should be seeing the S code in the results, obvsiously with a value of 0 as there are no entries.

Have I done the query the wrong way around (selecting from the wrong table first), or is it something to do with my JOINS? I have tried different combinations and keep getting the same results

Upvotes: 0

Views: 68

Answers (1)

axiac
axiac

Reputation: 72177

This query should work:

SELECT c.code, sd.code_value 
FROM codes c
LEFT JOIN submission_data sd  ON c.ID = sd.code_id AND submission_id = 1

It gets all the rows from codes. For each row from codes it finds all the matches (by code.ID) from submission_data that also have submission_id = 1. If no such row is found in submission_data, it returns NULL for sd.code_value.

Upvotes: 1

Related Questions