SQL Joins Excluding Data
- by Andrew
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 with Column 1: the set of fruit offered at Produce Store A UNION Produce Store B, Column 2: Price of the fruit at Produce Store A (or null if that fruit is not offered), Column 3: Price of the fruit at Produce Store B (or null if that fruit is not offered), how would I go about joining the tables?
I am facing a similar problem (with more complex tables), and no matter what I try, if the "fruit" is not at "produce store a" but is at "produce store b", it is excluded (since I am joining produce store a first). I have even written a subquery to generate a full list of fruits, then left join Produce Store A, but it is still eliminating the fruits not offered at A. Any Ideas?
