Reporting Help 1

[Freemo]

Hi,

I am having trouble creating a report in SQL. I have a table with client data in it which links to a table with telephone numbers for clients. Each client could have 3 different types of number, work, mobile or home. At present my report returns 3 rows of data for a client with 3 numbers but what i want is a seperate column for each number type and only 1 row per client.

I hope that makes sense!

Thanks in advance.

 

[RiverGuy]

Yep, here is some pseudocode to get you started:

SELECT Clients.*,
Work.Phone AS WorkPhone,
Mobile.Phone AS MobilePhone,
Home.Phone AS HomePhone
FROM Clients
LEFT OUTER JOIN Telephones Work ON Clients.ClientID = Work.ClientID AND Work.PhoneType = 'Work'
LEFT OUTER JOIN Telephones Mobile ON Clients.ClientID = Mobile.ClientID AND Mobile.PhoneType = 'Mobile'
LEFT OUTER JOIN Telephones Home ON Clients.ClientID = Home.ClientID AND Home.PhoneType = 'Home'

 

[Freemo]

Thanks for the reply. I should have put in the original post that all telephone numbers are in the same table and can be identified by an id number. I think that the above solution would work if 3 types had their own table?

Thanks again

 

[RiverGuy]

No, the above example is joining the same table three times and aliasing them as Work, Mobile and Home.

 

[RDCushing]

Try this:

select c.*
, (select Phone
from Telephones t
where t.ClientID = c.ClientID
and t.PhoneType = 'Work') as WorkPhone
, (select Phone
from Telephones t
where t.ClientID = c.ClientID
and t.PhoneType = 'Mobile') as MobilePhone
, (select Phone
from Telephones t
where t.ClientID = c.ClientID
and t.PhoneType = 'Home') as HomePhone
from Clients c

Richard D. Cushing
Sr. Consultant
SYNERGISTIC SOFTWARE SOLUTION, LLC
A Wholly-owned subsidiary of BDO Seidman, LLP