August 11, 2008 at 12:13 pm
How can I get this information to come back in one row?
Thomas Hainey 999999 NULL tlhainey@yahoo.com
Thomas Hainey 999999 thomas.hainey@aol.com NULL
I want it to look like this:
Thomas Hainey 999999 thomas.hainey@aol.com tlhainey@yahoo.com
Here is my sql code
select distinct name_master.first_name, name_master.last_name, name_master.id_num, address_master.phone,
name_master.mobile_phone,
CASE
WHEN ADDRESS_MASTER.ADDR_CDE = '*EML'
THEN address_master.addr_line_1
END AS 'CC_EMAIL ACCOUNT',
CASE
WHEN ADDRESS_MASTER.ADDR_CDE = 'PEML'
THEN address_master.addr_line_1
END AS 'OTHER EMAIL ACCOUNT'
from name_master join address_master on address_master.id_num = name_master.id_num
left outer join empl_mast on empl_mast.id_num = name_master.id_num
where EMPL_MAST.SUBGRP_CDE in ('STF','FAC','ADJ') and
(empl_mast.act_inact_sts = 'A') and
(address_master.addr_cde ='PEML'
OR ADDRESS_MASTER.ADDR_CDE = '*EML')
order by last_name
August 11, 2008 at 2:13 pm
nwinningham (8/11/2008)
How can I get this information to come back in one row?Thomas Hainey 999999 NULL tlhainey@yahoo.com
Thomas Hainey 999999 thomas.hainey@aol.com NULL
I want it to look like this:
Thomas Hainey 999999 thomas.hainey@aol.com tlhainey@yahoo.com
Here is my sql code
select distinct name_master.first_name, name_master.last_name, name_master.id_num, address_master.phone,
name_master.mobile_phone,
CASE
WHEN ADDRESS_MASTER.ADDR_CDE = '*EML'
THEN address_master.addr_line_1
END AS 'CC_EMAIL ACCOUNT',
CASE
WHEN ADDRESS_MASTER.ADDR_CDE = 'PEML'
THEN address_master.addr_line_1
END AS 'OTHER EMAIL ACCOUNT'
from name_master join address_master on address_master.id_num = name_master.id_num
left outer join empl_mast on empl_mast.id_num = name_master.id_num
where EMPL_MAST.SUBGRP_CDE in ('STF','FAC','ADJ') and
(empl_mast.act_inact_sts = 'A') and
(address_master.addr_cde ='PEML'
OR ADDRESS_MASTER.ADDR_CDE = '*EML')
order by last_name
Okay, the first thing I had to do was reformat your query to see what was going on. Here is the reformatted query that should get you the results you are looking for:
Select nm.first_name
,nm.last_name
,nm.id_num
,am.phone
,nm.mobile_phone
,am1.addr_line_1 As CC_Email_Account
,am2.addr_line_1 As Other_Email_Account
From name_master nm
Inner Join empl_mast em On em.id_num = nm.id_num
Left Join address_master am1 On am1.id_num = nm.id_num And am1.addr_cde = '*EML'
Left Join address_master am2 On am2.id_num = nm.id_num And am2.addr_cde = 'PEML'
Where em.subgrp_cde In ('STF','FAC','ADJ')
And em.act_inact_sts = 'A'
Order By nm.last_name;
The first thing I want to point out is the addition and usage of table aliases. The second thing I noticed is that you don't need an outer join to the empl_mast table. In fact, by using the empl_mast table in the where clause you were forcing the join to an inner join anyways.
So, to get your two email addresses I joined to the address_master twice (am1 and am2) and specified in each join the specific address code I wanted. I then had to change the type of join to an outer join, since there may not be an email address with that specific code.
And, I removed the distinct keyword because it really shouldn't be necessary. If there are duplicate entries in name_master, you should be able to find some way to uniquely identify which name you want in the results.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 11, 2008 at 2:35 pm
Thank you for your assistance.
August 11, 2008 at 2:40 pm
Did that solve the problem?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 11, 2008 at 2:41 pm
Yes it did.
August 11, 2008 at 9:01 pm
nwinningham (8/11/2008)
How can I get this information to come back in one row?Thomas Hainey 999999 NULL tlhainey@yahoo.com
Thomas Hainey 999999 thomas.hainey@aol.com NULL
Since I don't have all your tables or data to test with, let's concentrate on the basic question...
... simple GROUP BY will do it...
--===== Create and populate a demonstration table
-- This is NOT part of the solution
DECLARE @DemoTable TABLE
(FName VARCHAR(10), LName VARCHAR(10), ID INT, CCEmail VARCHAR(30), OtherEmail VARCHAR(30))
INSERT INTO @DemoTable
(FName, LName, ID, CCEmail, OtherEmail)
SELECT 'Thomas','Hainey','999999',NULL,'tlhainey@yahoo.com' UNION ALL
SELECT 'Thomas','Hainey','999999','thomas.hainey@aol.com',NULL UNION ALL
SELECT 'Jane','Doe','888888','jane.doe.com',NULL UNION ALL
SELECT 'John','Doe','777777',NULL,'johndoe@yahoo.com' UNION ALL
SELECT 'Yosemite','Sam','666666',NULL,'ysam@yahoo.com' UNION ALL
SELECT 'Yosemite','Sam','666666','yosemite.sam@aol.com',NULL
--===== Use a classic bit of Group By technology to produce the output.
SELECT FName, LName, ID,
MAX(CCEMail) AS CCEMail,
MAX(OtherEMail) AS OtherEMail
FROM @DemoTable
GROUP BY FName, LName, ID
--Jeff Moden
Change is inevitable... Change for the better is not.
August 12, 2008 at 12:47 pm
Thanks for your reply.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply