Say I have these three tables:
Table: Baskets
id | name
1 Sale
2 Premium
3 Standard
4 Expired
Table: Fruit
id | name | basketid
1 Apples 1
2 Oranges 2
3 Grapes 3
4 Apples 2
5 Apples 4
Table: Veggies
id | name | basketid
1 Carrots 1
2 Peas 2
3 Asparagus 1
It may seem like the second two tables should have just been one table called produce, but in the real situation there is good reason for them to be different tables. I need to write a join that returns rows if the basket has rows in either the fruit or veggie tables. I thought I could accomplish this with two left joins like so:
Select Baskets.*, fruit.name as fruit,
veggies.name as veggies
from Baskets
left join Fruit on Baskets.id = Fruit.basketid
left join veggies on Baskets.id = Veggies.basketid
where Baskets.id = 2;
But this statement returns values in fields that I would like to be empty. The actual output:
id | name | fruit | veggies
2 Premium Oranges Peas
2 Premium Apples Peas
The output I would like:
id | name | fruit | veggies
2 Premium Oranges
2 Premium Apples
2 Premium Peas
How 开发者_开发技巧can I accomplish this?
You need to union the Fruits and Vegetables table into one relation that you join against, a la:
Select Baskets.*, produce.fruitname as fruit, produce.veggiename as veggies
from Baskets
left join (SELECT basketid, name as fruitname, NULL as veggiename
FROM fruit
UNION
SELECT basketid, NULL, name
FROM veggies) produce
ON baskets.id = produce.basketid
where Baskets.id = 2;
This query will return no rows if there is nothing in the basket:
SELECT baskets.*, fruit.name AS fruit, NULL AS veggie
FROM baskets
JOIN fruit
ON fruit.basketId = baskets.id
WHERE baskets.Id = 2
UNION
SELECT baskets.*, NULL, veggie.name
FROM baskets
JOIN veggies
ON veggies.basketId = baskets.id
WHERE baskets.Id = 2
This query will return a single row with two NULLS
if there is nothing in the basket:
SELECT baskets.*, fruit.name AS fruit, NULL AS veggie
FROM baskets
LEFT JOIN
fruit
ON fruit.basketId = baskets.id
WHERE baskets.Id = 2
UNION
SELECT baskets.*, NULL, veggie.name
FROM baskets
LEFT JOIN
veggies
ON veggies.basketId = baskets.id
WHERE baskets.Id = 2
This assumes that both fruit.name
and veggie.name
are not nullable and unique.
This query is the same as previous but can be more efficient:
SELECT baskets.*, fruit.name AS fruit, NULL AS veggie
FROM baskets
JOIN fruit
ON fruit.basketId = baskets.id
WHERE baskets.Id = 2
UNION ALL
SELECT baskets.*, NULL, veggie.name
FROM baskets
JOIN veggies
ON veggies.basketId = baskets.id
WHERE baskets.Id = 2
UNION ALL
SELECT baskets.*, NULL, NULL
FROM baskets
LEFT JOIN
fruit
ON fruit.basketId = baskets.id
LEFT JOIN
veggies
ON veggies.basketId = baskets.id
WHERE baskets.Id = 2
AND fruit.basketId IS NULL
AND veggies.basketId IS NULL
, and does not assume anything.
精彩评论