CODEWITHSUNDEEP
mysqlsql
Defain
Defain

Reputation: 1325

SQL join returns all values from the wrong table

I am new to SQL and I cannot figure out why my SQL result returns all data from the wrong table. I want to receive all data from product table but I want to filter the results so it does not show those results that have some value in another table.

My SQL statement is:

SELECT * 
  FROM orders 
       JOIN products 
           ON orders.product_id=products.product_id 
           AND NOT order_date=somedate;

This query returns everything from the orders table where somedate is not the given value but I want to get everything from the products table if orders table does not have the given somedate as value (when the product is not reserved).

Edit: thanks for the help everyone! each answer worked perfectly for me with some slight modifications :)

Upvotes: 0

Views: 90

Answers (3)

Alexander
Alexander

Reputation: 3179

Not sure I got your question right, but you can try this:

SELECT p.* 
  FROM orders 
       JOIN products 
           ON orders.product_id=products.product_id 
 WHERE order_date<>somedate;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Different orders could have different dates. If you want products that were not ordered on a particular date, then try phrasing the query like this:

If I understand correctly, your original query returned all products with multiple orders, because some orders were on that date and some were not.

select p.* 
  from product p 
 where not exists (select 1 
                     from orders o 
                    where o.product_id = p.product_id 
                          and o.order_date = somedate)

Upvotes: 1

Vatev
Vatev

Reputation: 7590

You can get products which don't have orders of a certain criteria (order_date != somedate) like this:

SELECT p.*
FROM products p
LEFT JOIN orders o ON o.product_id = p.product_id AND order_date != somedate 
WHERE o.id IS NULL

Upvotes: 1

Related Questions