left join with empty results when no match is found

Question

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?

Answer 1

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;

Answer 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.