CODEWITHSUNDEEP
mysqlsql
Defain
Defain

Reputation: 1325

SQL statement returns only rows that exist also in the joined table

I cannot figure out how to form a proper SQL statement for this. I want to get every row from products table but filter the results if certain value in other table is larger than something.

My current statement returns every row that I want to filter correctly but I was unable to make it instead filter the current result and print everything else

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

I want it to print whole products table except filter row if orders table column order_date is smaller than the given date,

Edit: thanks for everyone who helped me with this!

Upvotes: 1

Views: 85

Answers (6)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

Printing "everything else" is not simply a negation of this query. The focus is on the products table and, presumably, you don't want duplicates. You could use except for this purpose. Here is another approach:

SELECT p.*
FROM products p
WHERE NOT EXISTS (select 1
                  from orders o
                  where o.product_id = p.product_id AND
                        o.order_date >= somedate
                 );

This will return products that do not have an order on or after the given date.

Note that this will return products that have no orders. I am unclear on what to do with those.

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 95080

You think too complicated. You want records from table products where a certain order not exists. So say NOT EXISTS in your where clause.

SELECT *
FROM products
WHERE NOT EXISTS
(
  SELECT *
  FROM orders
  WHERE product_id = products.product_id 
  AND order_date < somedate 
);

(You can solve this with an outer join, which alwyays looks a bit quirky in my eyes. Be aware: When outer joining, don't use the outer joined tables fields in the where clause, cause they would be null for an outer joined record of course.)

Upvotes: 2

Sharma Dhananjay
Sharma Dhananjay

Reputation: 437

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

Upvotes: 4

slapthelownote
slapthelownote

Reputation: 4279

Try: 

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

Upvotes: 1

KrazzyNefarious
KrazzyNefarious

Reputation: 3230

Whenever you need to filter the result set, mention the condition in WHERE clause. Whenever you need to filter the table you are joining with, mention the condition in JOIN Condition. Had it been an INNER JOIN, it would have worked. But the scenario is a lot different in case of LEFT OUTER and RIGHT OUTER JOIN.

So the query becomes:-

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

Upvotes: 3

StanislavL
StanislavL

Reputation: 57421

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

Just move the condition into the WHERE section

Upvotes: 3

Related Questions