CODEWITHSUNDEEP
phpmysql
swapfile
swapfile

Reputation: 417

MYSQL: Exclude Rows in join if row in join table matches

let's say i have the following two tables: 

Table Orders
ID | Order_ID | Products_ID
1       a          22912
2       b          22912
3       c          22912

Table Products
ID     | more stuff
22912     lorem ipsum

So far really easy. With joins i can get every info i want. But i want a joined query thats returns Null if a Product has a order from a sepcial order id. That means if i get a match on order id then null should be returned for the product.

i tried in different ways: 

SELECT * FROM Products p JOIN orders o ON p.ID=o.Products_ID WHERE Order_ID !='a'

with that i'll get the 2 for b & c order id.

Allright next try: 

select * from Products p JOIN (select * FROM Orders o WHERE WHERE Order_ID !="a") jo ON p.ID=jo.Product_ID

Hmm same query, same result. Result should be empty if one Order_ID matches. Anyone an Idea how to solve that within one query. I think the second query is the key but how to write the subquery that it returns empty result?

Upvotes: 0

Views: 97

Answers (4)

JDeka
JDeka

Reputation: 1

Try this query:

SELECT  * FROM  orders o  Left JOIN   Products p ON p.ID=o.Products_ID and o.Order_ID !='a'

Upvotes: 0

P.Salmon
P.Salmon

Reputation: 17640

select p.*, null as somecolumn
from p
join o on o.products_id = p.id
where o.order_id = 'a';

Returns

+-------+-------------+------------+
| ID    | morestuff   | somecolumn |
+-------+-------------+------------+
| 22912 | lorem ipsum |       NULL |
+-------+-------------+------------+
1 row in set (0.00 sec)

Fulfills your requirement if i get a match on order id then null should be returned for the product.But that doesn't make sense to me and there does not seem to be a relationship other than by product between orders a,b,c

Upvotes: 0

romal tandel
romal tandel

Reputation: 501

Try This

SELECT TEMP.* FROM (SELECT p.*,o.ORDER_ID FROM Products p JOIN orders o ON p.ID=o.PRODUCT_ID WHERE o.Order_ID ='a') as TEMP WHERE TEMP.ORDER_ID !='a'

Upvotes: 0

Ankur Vashisht
Ankur Vashisht

Reputation: 66

you can use the case statement, check the below code

 SELECT 
case
    when o.Order_ID = 'a'
        then 'null'
    else
        o.Order_ID 
end as orderId
 FROM Products p JOIN orders o 
 where p.ID = o.Products_ID

Upvotes: 1

Related Questions