I’m having a bit of difficulty using the union query exactly as I would like.
Say I have two tables, one containing my friends’ favorite liquors, and one with their favorite flowers. I want to combine the tables to make “gifts”: I prefer to buy flowers, but if there is no favorite flower in the table, I’ll buy their favorite liquor.
For example
Inputs:
LIQUORS TABLE
FRIEND LIQUOR
Alice Tequila
Bob Whiskey
FLOWERS TABLE
FRIEND FLOWER
Alice Roses
Cheryl Daisies
Then I want to do a query which displays
GIFTS TABLE
FRIEND GIFT
Alice Roses
Bob Whiskey
Cherly Daisies
I’ve gotten this to work before, but maybe I had primary keys set… how do I avoid the duplicating the Friend field?
You didn’t say what kind of database you’re using, which makes giving suggestions difficult.
I’d say your best bet is to select the appropriate entries in the queries before you merge the results. The syntax below may or may not work, depending on your database.
SELECT FRIEND, FLOWER as Gift FROM FLOWERS
UNION
SELECT FRIEND, LIQUOR as Gift FROM LIQUORS LEFT OUTER JOIN FLOWERS
ON FLOWERS.FRIEND = LIQUORS.FRIEND
WHERE FLOWERS.FRIEND IS NULL
It’s possible you could do a NVL to a subquery(by the way I think in Oracle sqlplus).
Something like:
Select friend, NVL(Flower,(select Liquor from liquor B where A.friend = B.friend) )from Flower A;
But I don’t have access to a DB right now to test it. I know I’ve tried to do things like that, But I can’t remember if I ever got it to work. I’ve tried a lot of wierd things.
Edit timeout before comepltion, trying again.
It’s possible you could do a NVL to a subquery(by the way I think in Oracle sqlplus).
Something like:
Select friend, NVL(Flower,(select Liquor from liquor B where A.friend = B.friend) )from Flower A;
Union
select friend, liquor from liquor where friend NOT IN (select friend from flower);
But I don’t have access to a DB right now to test it. I know I’ve tried to do things like that, But I can’t remember if I ever got it to work. I’ve tried a lot of wierd things.
This returned exactly what you wanted in SQL Server 2005:
SELECT COALESCE(f.friend, l.friend) AS ‘Friend’, COALESCE(f.flower, l.liquor) AS ‘Gift’
FROM #tmpFlowers F FULL JOIN #tmpLiquors l ON f.[Friend] = l.[Friend]
What a full join does is give you everything from both tables.
SELECT * FROM #tmpFlowers F FULL JOIN #tmpLiquors l ON f.[Friend] = l.[Friend]
Friend Flower Friend Liquor
Cheryl Daisies NULL NULL
Alice Roses Alice Tequila
NULL NULL Bob Whiskey
And the COALESCE gives you the first non null values from the list provided in the parenthesis.
Of course, there about 10 different ways to do this. If this was a hypothetical example for something you need to do in production, you may want to look at a few different ways, compare the execution plan and performance. If you do it this way, be careful about the order you put things in in the COALESCE - it is important.
Sorry about the edit - I wasn’t done typing and I accidentally posted
SQL> select * from flower;
CHUM FLOWER
---------- --------------------
Bob Red Roses
Alice Pansies
Edna Peones
SQL> select * from booze;
CHUM TIPPLE
---------- --------------------
Bob Gordons
Alice Laphraig
Charlie Absinthe
Dave Beer
Edna Cinzano
Fran Vodka Martini
Union query
select chum, tipple
from booze
where chum not in (select chum from flower)
union
select chum, flower
from flower
Result
CHUM TIPPLE
---------- --------------------
Alice Pansies
Bob Red Roses
Charlie Absinthe
Dave Beer
Edna Peones
Fran Vodka Martini
If you’re worried about the column headings alias ‘TIPPLE’ to ‘GIFT’
I’ve tried a lot of wierd things.