Optional AND in WHERE statement MySQL
I'm trying to figure out how to allow optional AND
statements where there is a LEFT OUTER JOIN
since the table is optional when viewing records. However, I have a problem where there is开发者_如何学运维 no files, and in the WHERE
statement, like this:
SELECT rec.record_id,
rec.record_name,
f.file_name,
f.file_id
FROM
(
records rec
LEFT OUTER JOIN files f ON f.record_id = rec.record_id
)
WHERE rec.record_id = 4928
AND f.file_approved = 1 <-- this is what returns a zero results
When I remove AND f.file_approved = 1
it returns a record, but when I leave it, it returns no record.
If a record contains no file records, it will not return anything. I need it to check it and if there are no files, it should still be able to return the record (without the files).
try moving the condition into the join statement, that way it will only join on the lines if they meet the condition
SELECT rec.record_id,
rec.record_name,
f.file_name,
f.file_id
FROM
(
records rec
LEFT OUTER JOIN files f ON f.record_id = rec.record_id AND f.file_approved = 1
)
WHERE rec.record_id = 4928;
If yu dont want to force the condition, use the OR instead
WHERE
rec.record_id = 4928
OR
f.file_approved = 1
精彩评论