May 21, 2007 at 3:04 am
Hi Guys,
I'm quite new to SQL and currently i'm playing around with some tables.
I'm trying to retrieve the contact details of individuals at suppliers in the logistics dept. Each contact at any given supplier may have one or more phone numbers and email addresses (for all intents and purposes).
My result set looks like the following, which i think is right.
supplier | dept | contact |email | telephone
HAMLI001 |LOGISTICS| DAVE ABRAHAM |NULL |91349639819
HAMLI001 |LOGISTICS| DAVE ABRAHAM |dave.abraham@palin.com |NULL
However, i would like to know if it's possible to merge the rows above (for example) into one line for each contact. Like so -
HAMI001 |LOGISTICS| DAVE ABRAHAM |dave.abraham@palin.com |91349639819
Is this possible, using some kind of 'group by' command - my statement looks like this so far
SELECT a.traderid, a.contactid, (firstname + ' ' + lastname) AS name, destination as email, fixednumber AS telephone FROM contactcomms a INNER JOIN suppcontacts b ON a.traderid = b.traderid AND a.contactid = b.contactid WHERE a.traderid = 'HAMI001' and a.contactid LIKE '%OGISTI%'ORDER BY name
Thanks in advice.
May 21, 2007 at 7:42 am
Looks like the SuppContacts table has one row for email with phone null & one row for phone with email null? Better design would probably be to seperate these into two tables. However:
SELECT a.traderid, ..., b.destination as email, c.fixednumber as telephone
FROM contactcoms a
INNER JOIN suppcontacts b ON a.traderid = b.traderid AND a.contactid = b.contactid and b.fixednumber IS NULL
INNER JOIN suppcontacts c ON a.traderid = c.traderid AND a.contactid = c.contactid and c.destination IS NULL
WHERE ...
This is likely to run slow. Also, everyone has to have both an email and a phone for this to work. You could make them OUTER JOINS, but I was following what you had. Now, when someone has two email addresses, you're still going to get two rows for that person. You might want to look into the PIVOT function and see if that can help you out.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 21, 2007 at 8:10 am
Hey thanks for the info - it's a start. I'll look into the pivot option and see what comes of it.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply