Geek asks and answers

Optional AND in MySQL WHERE clause

I am trying to figure out how to allow optional instructions AND, where there are LEFT OUTER JOIN, since the table is optional when viewing records. However, I have a problem when there are no files in the instructions WHERE, for example:

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 delete AND f.file_approved = 1, it returns the record, but when I leave it, it does not return the record.

If the record does not contain file records, it will not return anything. I need to check it, and if there are no files, it will still be able to return the record (without files).

+3
source share
2 answers

try moving the condition to the join operator, so it will only join strings if they satisfy 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;
+5

, OR,

WHERE 
  rec.record_id = 4928  
OR 
  f.file_approved = 1
0

More articles:


All Articles