September 23, 2008 at 3:09 pm
Hello All;
I hope you can help me with what I need to do.
When joining some tables I need the results to display on seperate rows and columns.
Example:
I have a CONTACTS table (Called Idividual) that has a phone number and a phone type (this indicates if the phone number is a fax, mobile etc)
There is also a CUSTOMER_PHONE table which contains phone numbers which relate back to the CONTACTS table
(This is not my DB and I know the Normalization is rubbish in this database )
I need to pull results that show the phone number from both tables for each CONTACT when the types are specific (101416, 101417 and 115)
I each result on its own line for example
Contact phone417 phone416 phone115
ContactA # # #
ContactB #
ContactC # #
My diagram is trying to show that CONTACTS don't have all phone types:
I do have a script like this:
USE ONYX
SELECT
Company.iCompanyID AS Company_ID,
vchCompanyName AS Company_Name,
Company.iPhoneTypeId AS Company_PhoneTypeID,
Company.vchPhoneNumber as Company_Phone_Number,
Customer_Phone.iPhoneTypeId AS Customer_Phone_PhoneTypeID,
Customer_Phone.vchPhoneNumber as Customer_Phone_Phone_Number
FROM Company
LEFT OUTER JOIN Customer_Phone
ON Customer_Phone.iOwnerId=Company.iCompanyID
WHERE
(Company.vchUser10 IS NULL)
AND (Company.tiRecordStatus = 1)
AND (Company.bPrivate = 0)
AND (Company.bValidAddress = 1)
AND ((Company.iPhoneTypeId IN ('101417', '101416', '115') OR (Customer_Phone.iPhoneTypeId IN ('101417', '101416', '115'))))
This doesn't return in a format I need
I spoke to someone who used IIF in Access on his DB and it worked but I couldn't get it working in SQL 2005
If anyone can help I'd appreciate it and if you need more details I'll give them to you.
Thanks so much
Ian
September 24, 2008 at 2:16 am
I'm not sure I fully understand your schema and/or requirements, but this may be what you are after:-
SELECT
Company.iCompanyID AS Company_ID,
vchCompanyName AS Company_Name,
Company.iPhoneTypeId AS Company_PhoneTypeID,
Company.vchPhoneNumber as Company_Phone_Number,
p417.vchPhoneNumber as Phone_417,
p416.vchPhoneNumber as Phone_416,
p115.vchPhoneNumber as Phone_115
FROM Company
LEFT OUTER JOIN Customer_Phone p417
ON Customer_Phone.iOwnerId=Company.iCompanyID AND Customer_Phone.iPhoneTypeId = '101417'
LEFT OUTER JOIN Customer_Phone p416
ON Customer_Phone.iOwnerId=Company.iCompanyID AND Customer_Phone.iPhoneTypeId = '101416'
LEFT OUTER JOIN Customer_Phone p115
ON Customer_Phone.iOwnerId=Company.iCompanyID AND Customer_Phone.iPhoneTypeId = '115'
WHERE
(Company.vchUser10 IS NULL)
AND (Company.tiRecordStatus = 1)
AND (Company.bPrivate = 0)
AND (Company.bValidAddress = 1)
If it isn't, post details of the schema and some sample data.
September 26, 2008 at 9:54 am
Hello,
First and foremost thanks for replying.
I have tried to put some data on this to sort of show what I am trying to do.
The two tables that are involved both contain phone type ids (ie Fax, Mobile etc) and phone numbers - which I understand is dumb - again I didn't design this - it is a DB from a company called ONYX
So the Contact Table may have
iCompanyID Name PhoneType PhoneNumber
1 Bill 417 555-1234
2 Bob 416 555-4321
3 Frank 115 555-7894
4 John 115 555-1111
The Phone_number table has:
iOwnerID PhoneType PhoneNumber
1 416 555-7777
2 417 555-6666
3 417 555-5555
4 416 555-4444
1 115 555-3333
2 115 555-1111
I am trying to get a select statement to return a table such as follows
CompanyID Name PhoneNumbers417 PhoneNumbers416 PhoneNumbers115
1 Bill 555-1234 555-7777 555-3333
2 Bob 555-6666 555-4321 555-1111
3 Frank 555-5555 555-7894
4 John 555-4444 555-1111
I hope this makes some sort of sense for you 🙂
Thanks for anything you can help me with.
The script you gave told me that column couldn't be bound when I tried to run it.
Thanks so much again
September 30, 2008 at 2:00 am
This should give you what you need:-
select icompanyid,name,
case when c.phonetype = 417 then c.phonenumber
else p417.phonenumber end as PhoneNumbers417,
case when c.phonetype = 416 then c.phonenumber
else p416.phonenumber end as PhoneNumbers416,
case when c.phonetype = 115 then c.phonenumber
else p115.phonenumber end as PhoneNumbers115
from company c
left join phone_number p417 on c.icompanyid = p417.iownerid
and p417.phonetype = 417
left join phone_number p416 on c.icompanyid = p416.iownerid
and p416.phonetype = 416
left join phone_number p115 on c.icompanyid = p115.iownerid
and p115.phonetype = 115
October 1, 2008 at 7:18 am
This should also work for you. It uses the built in pivot function, and it save join and case statements if you wish to add another phone type to the statement. Just list it in your where clause, Add it to the list of phone types in the pivot function, and to the top select statement for it's own column.
USE ONYX
Select
Company_ID
,Company_Name
,Company_PhoneTypeID
,Company_Phone_Number
,Customer_Phone_PhoneTypeID
,Customer_Phone_Phone_Number
,[101417] As PhoneNumbers417
,[101416] As PhoneNumbers416
,[115] As PhoneNumbers115
From
(
SELECT
Company.iCompanyID AS Company_ID,
vchCompanyName AS Company_Name,
Company.iPhoneTypeId AS Company_PhoneTypeID,
Company.vchPhoneNumber as Company_Phone_Number,
Customer_Phone.iPhoneTypeId AS Customer_Phone_PhoneTypeID,
Customer_Phone.vchPhoneNumber as Customer_Phone_Phone_Number,
1 As MyCount --Added This Column As Pivot Requires Something To Agrigate
FROM Company
LEFT OUTER JOIN Customer_Phone
ON Customer_Phone.iOwnerId=Company.iCompanyID
WHERE
(Company.vchUser10 IS NULL)
AND (Company.tiRecordStatus = 1)
AND (Company.bPrivate = 0)
AND (Company.bValidAddress = 1)
AND ((Company.iPhoneTypeId IN ('101417', '101416', '115') OR (Customer_Phone.iPhoneTypeId IN ('101417', '101416', '115'))))
)A
Pivot(
Count(MyCount)
For Company_PhoneTypeID in ( --List the different phone types here
[101417]
, [101416]
, [115]
)
)B
October 1, 2008 at 7:51 am
Because joining on the same table multiple times can be slow, you may want to try SQL2005's pivot, something like...
SELECT iCompanyID AS Company, [Name],
[417] AS [417 Number],
[416] AS [416 Number],
[115] AS [115 Number]
FROM
( SELECT iCompanyId, [Name], PhoneType, PhoneNumber
FROM dbo.Contact
UNION
SELECT iOwnerId, c.[Name], p.PhoneType, p.PhoneNumber
FROM dbo.Phone_Number p
JOIN dbo.Contact c
ON c.iCompanyId = p.iOwnerId
) main
PIVOT
(MAX(PhoneNumber) FOR PhoneType IN
([115], [416], [417])
-- or is this ([101417], [101416], [115]) ?
) pvt
October 1, 2008 at 7:59 am
Performing self-joins should not be any slower than joining on other tables as long as you have appropriate indexes. Also check out this article about PIVOT: http://www.sqlservercentral.com/articles/T-SQL/63681/
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 1, 2008 at 8:16 am
In my case, I was joining on the primary key. Maybe I missed something, but I didn't have to do any totaling, and pivot was light years faster for me.
Ian, I think we all assumed from your example that you have only one ph # per slot, so I took the max of that one number. See what option works best for you.
Now all we need is someone named Ian Corb[e/i]tt to chime in, with all the Ians and Corbetts on this one.
October 1, 2008 at 8:22 am
dcorbitt (10/1/2008)
Now all we need is someone named Ian Corb[e/i]tt to chime in, with all the Ians and Corbetts on this one.
Yeah, what is upperbognor doing on this thread?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 1, 2008 at 9:13 am
I agree with using PIVOT, and as the two tables are identical save the Name field and the number of records, they can be UNION ALL'ed together, then pivoted. Here's my version of it, with the original sample data:
DECLARE @COMPANY TABLE (
iCompanyID smallint,
[Name] varchar(20),
PhoneType smallint,
PhoneNumber varchar(8)
)
INSERT INTO @COMPANY
SELECT 1,'Bill',417,'555-1234' UNION ALL
SELECT 2,'Bob',416,'555-4321' UNION ALL
SELECT 3,'Frank',115,'555-7894' UNION ALL
SELECT 4,'John',115,'555-1111'
DECLARE @PHONE_NUMBER TABLE (
iOwnerID smallint,
PhoneType smallint,
PhoneNumber varchar(8)
)
INSERT INTO @PHONE_NUMBER
SELECT 1,416,'555-7777' UNION ALL
SELECT 2,417,'555-6666' UNION ALL
SELECT 3,417,'555-5555' UNION ALL
SELECT 4,416,'555-4444' UNION ALL
SELECT 1,115,'555-3333' UNION ALL
SELECT 2,115,'555-1111'
;WITH COMBINED AS (
SELECT *
FROM @COMPANY
UNION ALL
SELECT iOwnerID, (SELECT [Name] FROM @COMPANY WHERE iCompanyID = P.iOwnerID),
PhoneType, PhoneNumber
FROM @PHONE_NUMBER AS P
)
SELECT iCompanyID, [Name], [417], [416], [115]
FROM COMBINED
PIVOT(MIN(PhoneNumber) FOR PhoneType IN ([115],[416],[417])) AS PVTBL
Short and sweet, producing the results as follows:
iCompanyIDName 417 416 115
1 Bill555-1234 555-7777 555-3333
2 Bob555-6666 555-4321 555-1111
3 Frank555-5555 NULL 555-7894
4 JohnNULL 555-4444 555-1111
Steve
(aka smunson)
:):):)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 1, 2008 at 10:50 am
Jack, Ian and Simon,
Thanks so much for all of you putting in the time and effort to help me with essentially my problem 🙂
I will test all of your suggestions against the data and let you know the outcome as I am sure your all eager to know 😀
Cheers
Ian
October 1, 2008 at 11:12 am
Hello;
There are over 100,000 records in the Customer_Phone table which is the table that relates back to the Company Table and house the additional numbers and types
It is feasible that a Company has more than 1 of each type of phone number; but this should be really rare, I hope 🙂
The last alternate suggestion posted involved an temporary table - using this I'd have to insert the 1000's of records correct? That looks like a seriouslly large amount of work 🙂
Again I thank you all for your input and I have no doubt everyone will hear from me again, I sincerely hope that at somepoint I can offer useful help such as you have.
Cheers
Ian
October 1, 2008 at 11:22 am
Mine was the last posted example, and it uses a CTE, or Common Table Expression, as opposed to a temporary table. The two table variables are only there to represent your sample data, and in the actual query, would be replaced with your real table names, so there's no need to insert 1000's of records. If ANY of the users have more than one phone number of a given type, the pivot method will NOT produce an accurate result, and will NOT include all but one of any given user's phone numbers of a given type.
You could easily determine whether any such situations exist ahead of time. Also, with those results in hand, it would be fairly easy to take those results and UNION them together with the pivot methods results, after some amount of manipulation. Without an actual table in hand, it would be a bit hard to code all of that, but it's do-able.
Steve
(aka smunson)
:):):)
Ian McQuade (10/1/2008)
Hello;There are over 100,000 records in the Customer_Phone table which is the table that relates back to the Company Table and house the additional numbers and types
It is feasible that a Company has more than 1 of each type of phone number; but this should be really rare, I hope 🙂
The last alternate suggestion posted involved an temporary table - using this I'd have to insert the 1000's of records correct? That looks like a seriouslly large amount of work 🙂
Again I thank you all for your input and I have no doubt everyone will hear from me again, I sincerely hope that at somepoint I can offer useful help such as you have.
Cheers
Ian
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply