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?
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
Try this query:
SELECT * FROM orders o Left JOIN Products p ON p.ID=o.Products_ID and o.Order_ID !='a'
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'
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