Ted Wilmont Ted Wilmont - 6 months ago 10
PHP Question

Also including NULL rows in MySQL SELECT Query

We are trying to select from several tables on a MySQL database.

Our query is:

SELECT r.reviews_id,
r.customers_name,
r.date_added,
rd.reviews_text,
r.reviews_rating,
c.customers_email_address
FROM reviews r, reviews_description rd, customers c
WHERE r.customers_id = c.customers_id
AND r.reviews_id = rd.reviews_id
AND r.products_id = '74'
AND r.approved='1'
ORDER BY LENGTH(rd.reviews_text) DESC


However, the table
reviews
also has NULL entries on the field
customers_id


The command
WHERE r.customers_id = c.customers_id
is limiting it to only fields that have a
customers_id


I was wondering how we can also select the NULL fields?

We have tried
WHERE (r.customers_id = c.customers_id) OR (r.customers_id IS NULL)
- but this does not work.

Thank you for your help in advance.

Answer

Don't use the old implicit join syntax. Use explicit joins - in this case a left join

select r.reviews_id, r.customers_name, r.date_added, rd.reviews_text, r.reviews_rating, 
       c.customers_email_address 
FROM reviews r
INNER JOIN reviews_description rd ON r.reviews_id = rd.reviews_id
LEFT JOIN customers c ON r.customers_id = c.customers_id 
WHERE r.products_id = '74' 
AND r.approved='1' 
ORDER BY LENGTH(rd.reviews_text) DESC