Reputation: 19
In MYSQL, let's say I have following two tables.
"orders":
id | name | customer_id | type | id_fabric
--------------------------------------------------------
1 | "P1601" | 0 | 1 | 1
2 | "M6451" | 0 | 2 | 2
3 | "T8200" | 8 | 1 | 1
4 | "R7441" | 0 | 2 | 2
5 | "S8018" | 2 | 1 | 3
6 | "P1240" | 7 | 1 | 3
"fabrics":
id | color | remaining
----------------------------
1 | black | 3.40
2 | red | 16
3 | navy | 12
I would like to create a query to retrieve only the fabrics whose remaining is greater than 5, which don't have any orders with customer_id = 0
of and whose order type is not equal to 2
So in this case the result would be:
id | color | remaining
------------------------------------------------
3 | navy | 12
I've tried to achieve this by using this following Sql query, but it doesn't get the expected result:
SELECT
color,
remaining
FROM
fabrics
LEFT JOIN orders ON id_fabric = id
WHERE
remaining > 2 AND id IN(
SELECT
id_fabric
FROM
orders
WHERE
type != 2 AND customer_id != 0
)
GROUP BY
id
Any idea ?
Upvotes: 1
Views: 138
Reputation: 10711
Use NOT EXISTS
SELECT
f.color,
f.remaining
FROM fabrics f
WHERE f.remaining > 5 AND
NOT EXISTS(
SELECT 1
FROM orders o
WHERE o.id_fabric = f.id AND
o.type = 2 AND o.customer_id = 0
)
Note that also JOIN and GROUP BY can be omitted from your original query.
Upvotes: 2
Reputation: 37493
You can try using correlated subquery
SELECT
color,
remaining
FROM
fabrics
LEFT JOIN orders ON id_fabric = id where remaining > 5 and
not exists (select 1 from orders o where o.id_fabric=fabrics.id and o.customer_id=0 and o.type=2)
Upvotes: 0
Reputation: 504
I don't think that subselect in your where condition is necessary in this case. You could simply go ahead like
SELECT
color,
remaining
FROM
fabrics
LEFT JOIN orders ON id_fabric = id
WHERE
remaining > 2
AND customer_id!=0
AND type != 0
GROUP BY
id
Upvotes: 0
Reputation: 1529
Using JOIN
and WHERE
condition and 'GROUP BY`
id
is same filed in both tables so use the alies name for tables.
select f.id,f.color,f.remaining
from fabrics f
join orders o on o.id_fabric = f.id
where f.remaining > 2 and o.type !=2 and o.customer_id!=0
group by f.id
Upvotes: 0
Reputation: 32021
try like below
SELECT
color,
remaining
FROM
fabrics
JOIN orders ON id_fabric = id
where customer_id!=0 and remaining>5 and type!=2
Upvotes: 0