January 31, 2013 at 12:01 pm
I have 3 Tables, one for Company, one for Contact and one for Phone Numbers. Each Company can have multiple Contacts. And Each Company can have multiple Phone Numbers.
In addition each Contact can have multiple Phone Numbers.
I am trying to write a query from the Company point of view that would list the Company, the Phone Numbers for that Company, and the Contacts for that Company each with its Phone Numbers.
I have tried just doing a SELECT and joining the tables, but I get a Company listed multiple times for each of the Contacts and Phone Numbers. Is there anyway to consolidate this information so I just get the Company listed once with all the contacts and phone numbers associated with it?
Thanks
January 31, 2013 at 12:16 pm
BLombardi (1/31/2013)
I have 3 Tables, one for Company, one for Contact and one for Phone Numbers. Each Company can have multiple Contacts. And Each Company can have multiple Phone Numbers.In addition each Contact can have multiple Phone Numbers.
I am trying to write a query from the Company point of view that would list the Company, the Phone Numbers for that Company, and the Contacts for that Company each with its Phone Numbers.
I have tried just doing a SELECT and joining the tables, but I get a Company listed multiple times for each of the Contacts and Phone Numbers. Is there anyway to consolidate this information so I just get the Company listed once with all the contacts and phone numbers associated with it?
Thanks
That is how it works. What would you want in the Company column? The type of thing you are describing is formatting which is best left to the front end. You could force this if you really must have your data returned like that.
In order to help you with the query we need to have ddl, sample data and desired output. Please take a few minutes and read the article found at the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 31, 2013 at 6:24 pm
I'll be a sport and setup some data to show you how you can display contacts and phones as a delimited list:
DECLARE @Company TABLE (CompanyName VARCHAR(100))
DECLARE @Contacts TABLE (CompanyName VARCHAR(100), ContactName VARCHAR(100))
DECLARE @Phones TABLE (CompanyName VARCHAR(100), Phone VARCHAR(40))
INSERT INTO @Company SELECT 'Farm Stores' UNION ALL SELECT 'AT&T'
INSERT INTO @Phones
SELECT 'Farm Stores', '5551212' UNION ALL SELECT 'Farm Stores', '5551213'
UNION ALL SELECT 'AT&T', '5551212' UNION ALL SELECT 'AT&T', '5551213'
UNION ALL SELECT 'AT&T', '5551213'
INSERT INTO @Contacts
SELECT 'Farm Stores', 'Dwain' UNION ALL SELECT 'Sean', '5551213'
UNION ALL SELECT 'AT&T', 'Jeff' UNION ALL SELECT 'AT&T', 'Chris'
UNION ALL SELECT 'AT&T', 'Sammy'
SELECT CompanyName, Phones=STUFF((
SELECT ',' + Phone FROM @Phones b WHERE a.CompanyName = b.CompanyName
FOR XML PATH('')), 1, 1, '')
,Contacts=STUFF((
SELECT ',' + ContactName FROM @Contacts b WHERE a.CompanyName = b.CompanyName
FOR XML PATH('')), 1, 1, '')
FROM @Company a
If that doesn't work for you, let us know how you would like them to format per company row.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply