Reply
Thread Tools Display Modes
#1
Old 01-16-2008, 10:49 AM
Guest
Join Date: Feb 2004
Location: NorCal
Posts: 996
SQL, Union queries, and removing "duplicates"

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:
Code:
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
Code:
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?

Last edited by iwakura43; 01-16-2008 at 10:50 AM.
#2
Old 01-16-2008, 11:06 AM
Guest
Join Date: Mar 2000
Posts: 2,926
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
#3
Old 01-16-2008, 11:42 AM
Guest
Join Date: Mar 2000
Posts: 10,345
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.

Last edited by wolfman; 01-16-2008 at 11:44 AM.
#4
Old 01-16-2008, 11:44 AM
Guest
Join Date: Oct 2003
Location: Milton Keynes
Posts: 2,555
I think you could use a subquery of

Code:
WHERE NOT EXISTS <select friend by flower query here>
to pull out flowers in preference to booze.

I'm supposed to be writing real code at work, maybe back later if I get time.
#5
Old 01-16-2008, 11:48 AM
Guest
Join Date: Mar 2000
Posts: 10,345
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.
#6
Old 01-16-2008, 12:20 PM
Guest
Join Date: Feb 2005
Location: Phoenix, Arizona, USA
Posts: 231
Quote:
Originally Posted by iwakura43
I'm having a bit of difficulty using the union query exactly as I would like.
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

Last edited by rainwalker78; 01-16-2008 at 12:25 PM.
#7
Old 01-16-2008, 12:20 PM
Guest
Join Date: Jun 2002
Posts: 6,915
Quote:
Originally Posted by redtail23
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
Yeah, this should work(it does on Postgres).
#8
Old 01-16-2008, 12:28 PM
Guest
Join Date: Oct 2003
Location: Milton Keynes
Posts: 2,555
My data
Code:
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
Code:
select chum, tipple
from booze
where chum not in (select chum from flower)
union
select chum, flower
from flower
Result
Code:
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'

Last edited by Small Clanger; 01-16-2008 at 12:31 PM.
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump


All times are GMT -5. The time now is 11:36 PM.

Copyright © 2017
Best Topics: stoop to conquer sublime directory stories quake symbol meaning usps postmark time diy chimney liner pronounce avoirdupois binky definition chalk body wet brake a similar vein sulphur candles pecker tracks lebanese tea tires hydroplaning football hard count hydrofluoric acid death peppermint allergy what is outcall grecian grey cable modem firewall wwii parachute mayonnaise raw egg applesauce expiration pigs penis boob rash seth amos sawed off rifle martin arriola larry david cape edinburgh vampire hive 7 quarter re breaking bones short change artists cecil adams slave trader marks dmv vision test cheat bench press every other day girl on blind faith album cover hats with c on it what does drano do deserve has nothing to do with it best athletes foot medication is mexico a western country bakers and chefs pans sloping shoulders vs square shoulders how to get into a union apprenticeship 80s ski movie south park mass effect 2 dlc missing ford taurus transmission fluid change replace blower motor furnace no hair on legs male paying rent in cash legal do first aid kits expire how much does ace charge to cash checks what are dead man's fingers dog licking other dog shotgun storage for home defense leave it to beaver ward send check in mail what happens when a cemetery fills up bird sleeping head under wing shower stool for shaving legs why do i suck at fps what is projectile vomiting can you die from a rattlesnake bite