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
Problems are opportunities brilliantly disguised as insurmountable obstacles.
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
Problems are opportunities brilliantly disguised as insurmountable obstacles.
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
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