CODEWITHSUNDEEP
mysqljoinleft-join
Ryan
Ryan

Reputation: 89

Using MySQL to return results using LEFT JOIN where one of the tables does not contain a record

This is a bit of a random question, and I've been doing this using multiple queries in MySQL and a small bit of PHP.

I have a MySQL query: 

SELECT * 
FROM (reviews LEFT JOIN orders ON reviews.orderid = orders.orderid) 
LEFT JOIN customers ON orders.custid = customers.id 
WHERE customers.email = '$email'

which returns all product reviews from a specific customer.

I would like to know structure a separate query which would return all of the customer's orders which are not yet reviewed. That is, where there is no record in the reviews table for a particular orderid.

So... 

SELECT * 
FROM orders LEFT JOIN customers ON orders.custid = customers.id 
WHERE customers.email = '$email'

... which will return all customer orders, but I then want to perhaps use another WHERE clause and a LEFT JOIN so that now only orderid's with no corresponding review records are returned. I've been trying to find something that will do this for some time now without any luck. Nothing seems to do the job.

As stated above, I have managed to do this using a combination of PHP/MySQL, however, it isn't very efficient, so I wondered if anyone had any suggestions?

Thanks in advance.

Ryan

Upvotes: 0

Views: 105

Answers (5)

Alex
Alex

Reputation: 2420

This will work but it could be slow.

SELECT * 
FROM orders 
LEFT JOIN customers 
  ON orders.custid = customers.id 
WHERE customers.email = '$email'
AND orders.orderid NOT IN (SELECT orderid FROM reviews)

This would be faster.

SELECT orders.orderid 
FROM orders 
LEFT JOIN customers 
  ON orders.custid = customers.id 
LEFT JOIN reviews
  ON reviews.orderid = orders.orderid
WHERE customers.email = '$email'
GROUP BY orders.orderid
HAVING count(reviews) = 0

Upvotes: 0

shaunhusain
shaunhusain

Reputation: 19748

SELECT * 
FROM customers 
  JOIN orders 
    ON orders.custid = customers.id 
WHERE customers.email = '$email' 
  AND orders.orderid NOT IN 
        ( SELECT orderid FROM reviews )

... maybe

Upvotes: 1

mathematical.coffee
mathematical.coffee

Reputation: 56905

You can LEFT JOIN your customers/orders onto reviews and select rows with no corresponding review:

SELECT * 
FROM orders LEFT JOIN customers ON orders.custid = customers.id 
LEFT JOIN reviews ON reviews.orderid = orders.orderid
WHERE customers.email = '$email'
 AND reviews.orderid IS NULL

Upvotes: 0

John Woo
John Woo

Reputation: 263683

try this:

SELECT * 
FROM  orders a LEFT JOIN customers b ON a.custid = b.id 
WHERE b.email = '$email' AND
      a.orderid NOT IN (SELECT orderid FROM reviews)

Upvotes: 0

dweeves
dweeves

Reputation: 5605

did you try this ?

SELECT orders.* FROM orders 
LEFT JOIN customers ON orders.custid = customers.id
LEFT JOIN reviews ON reviews.orderid=orders.id
 WHERE customers.email = '$email' AND reviews.orderid IS NULL

this should return all unreviewed orders for the customer of email $email

Upvotes: 1

Related Questions