SQL connections excluding data
Let's say I have three tables:
Fruit (Table 1) ------ Apple Orange Pear Banana Produce Store A (Table 2 - 2 columns: Fruit for sale => Price) ------------------------- Apple => 1.00 Orange => 1.50 Pear => 2.00 Produce Store B (Table 3 - 2 columns: Fruit for sale => Price) ------------------------ Apple => 1.10 Pear => 2.50 Banana => 1.00
If I would like to write a query in Column 1: A set of fruits offered in product store A UNION Produce Store B, Column 2: Price for fruits in grocery store A (or null if these fruits are not offered), Column 3: Price for fruits in store B product (or zero if this fruit is not offered), how can I join the tables?
I faced a similar problem (with more complex tables) and no matter what I try, if the "fruit" is not in the "manufacturing store" but is in the "product store b", it is excluded (since I first join grocery store). I even wrote a subquery to create a complete list of fruits and then left a connection to a Store A product, but it still removes fruits not offered in A. Any ideas?
a source to share
The key is to use left join
. This will include columns from the left table even if there is no matching row in the right table.
For instance:
select f.name, a.price, b.price
from Fruit f
left join ProduceStoreA a on a.Name = f.Name
left join ProduceStoreB b on b.Name = f.Name
If there is no fruit in store A, the second column will be null
. If it is not found in Store B, the third column will be null
. If neither of them sell fruit, both columns two and three will be null
.
a source to share