May 28, 2012 at 11:39 am
Hello I need help with a query, I really have no idea on how to do it
I have 3 tables
CLIENTS
id_client
name
address
PHONES
id_phone
description (Home, office, cellphone)
CLIENTS_PHONES
id_client
id_phone
number
What I want is a query which results in this:
name address homephone officephone cellphone
The only thing that I have managed to do is the following
name address homephone
name address officephone
name address cellphone
Please help, I really need this!
Thank you
May 28, 2012 at 12:03 pm
Something like this should work:
;WITH ctePhones AS
(
Select cp.id_client,
cp.number,
p.description
From CLIENTS_PHONES As cp
JOIN PHONES As p
ON cp.id_phone = p.id_phone
)
SELECT name,
[address],
(Select number From ctePhones cp
Where cp.id_phone = c.id_phone And cp.description = 'Home'
) As [homephone],
(Select number From ctePhones cp
Where cp.id_phone = c.id_phone And cp.description = 'office'
) As [officephone],
(Select number From ctePhones cp
Where cp.id_phone = c.id_phone And cp.description = 'cellphone'
) As [cellphone]
FROM CLIENTS As c
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 28, 2012 at 12:06 pm
Hi,
Try:
with
CTE_h as
(
select cp.id_client, cp.number
from CLIENTS_PHONES as cp
join PHONES as p on (p.id_phone = cp.id_phone) and
(p.description = 'Home')
),
CTE_o as
(
select cp.id_client, cp.number
from CLIENTS_PHONES as cp
join PHONES as p on (p.id_phone = cp.id_phone) and
(p.description = 'home')
),
CTE_c as
(
select cp.id_client, cp.number
from CLIENTS_PHONES as cp
join PHONES as p on (p.id_phone = cp.id_phone) and
(p.description = 'cellphone')
)
select
t.name,
t.address,
h.number as homephone,
o.number as officephone,
c.number as cellphone
from Clients as t
left join CTE_h as h on h.id_client = t.id_client
left join CTE_o as o on o.id_client = t.id_client
left join CTE_c as c on c.id_client = t.id_client
Hope this helps.
May 28, 2012 at 7:13 pm
RBarryYoung (5/28/2012)
Something like this should work:
;WITH ctePhones AS
(
Select cp.id_client,
cp.number,
p.description
From CLIENTS_PHONES As cp
JOIN PHONES As p
ON cp.id_phone = p.id_phone
)
SELECT name,
[address],
(Select number From ctePhones cp
Where cp.id_phone = c.id_phone And cp.description = 'Home'
) As [homephone],
(Select number From ctePhones cp
Where cp.id_phone = c.id_phone And cp.description = 'office'
) As [officephone],
(Select number From ctePhones cp
Where cp.id_phone = c.id_phone And cp.description = 'cellphone'
) As [cellphone]
FROM CLIENTS As c
Barry,
I humbly apologize for having to say this but I could only get your solution to work by changing id_phone in the subqueries to id_client, like this:
;WITH ctePhones AS
(
Select cp.id_client,
cp.number,
p.id_phone,
p.description
From CLIENTS_PHONES As cp
JOIN PHONES As p
ON cp.id_phone = p.id_phone
)
SELECT name,
[address],
(Select number From ctePhones cp
Where cp.id_client = c.id_client and cp.description = 'Home'
) As [homephone],
(Select number From ctePhones cp
Where cp.id_client = c.id_client and cp.description = 'office'
) As [officephone],
(Select number From ctePhones cp
Where cp.id_client = c.id_client and cp.description = 'cellphone'
) As [cellphone]
FROM CLIENTS As c
I would also like to humbly submit a more concise version:
DECLARE @CLIENTS TABLE (id_client INT, name VARCHAR(20), address VARCHAR(50))
DECLARE @PHONES TABLE (id_phone INT, description VARCHAR(10))
DECLARE @CLIENTS_PHONES TABLE (id_client INT, id_phone INT, number VARCHAR(20))
INSERT INTO @CLIENTS
SELECT 1, 'Dwain', 'Thailand' UNION ALL SELECT 2, 'Jeff', 'USA'
UNION ALL SELECT 3, 'Tom', 'Germany'
INSERT INTO @PHONES
SELECT 1, 'Home' UNION ALL SELECT 2, 'Office' UNION ALL SELECT 3, 'Cellphone'
INSERT INTO @CLIENTS_PHONES
SELECT 1, 1, '555-1212' UNION ALL SELECT 1, 2, '555-1213'
UNION ALL SELECT 2, 1, '555-1214' UNION ALL SELECT 2, 2, '555-1215' UNION ALL SELECT 2, 3, '555-1216'
SELECT Name, MAX(Address)
,homephone=MAX(CASE description WHEN 'Home' THEN number ELSE NULL END)
,officephone=MAX(CASE description WHEN 'Office' THEN number ELSE NULL END)
,cellphone=MAX(CASE description WHEN 'Cellphone' THEN number ELSE NULL END)
FROM @CLIENTS c
LEFT JOIN @CLIENTS_PHONES cp ON c.id_client = cp.id_client
LEFT JOIN @PHONES p ON p.id_phone = cp.id_phone
GROUP BY Name
If I expand the test harness to 1,000 rows (Jeff would tell me use 1,000,000 but the results already seem apparent), like this:
DECLARE @CLIENTS TABLE (id_client INT, name VARCHAR(20), address VARCHAR(50))
DECLARE @PHONES TABLE (id_phone INT, description VARCHAR(10))
DECLARE @CLIENTS_PHONES TABLE (id_client INT, id_phone INT, number VARCHAR(20))
INSERT INTO @CLIENTS
SELECT 1, 'Dwain', 'Thailand' UNION ALL SELECT 2, 'Jeff', 'USA'
UNION ALL SELECT 3, 'Tom', 'Germany'
INSERT INTO @PHONES
SELECT 1, 'Home' UNION ALL SELECT 2, 'Office' UNION ALL SELECT 3, 'Cellphone'
INSERT INTO @CLIENTS_PHONES
SELECT 1, 1, '555-1212' UNION ALL SELECT 1, 2, '555-1213'
UNION ALL SELECT 2, 1, '555-1214' UNION ALL SELECT 2, 2, '555-1215' UNION ALL SELECT 2, 3, '555-1216'
DECLARE @rows INT
SET @rows = 1000
;WITH Tally (n) AS (
SELECT TOP (@rows) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2)
INSERT INTO @CLIENTS
SELECT (id_client * @rows) + n
,Name + RIGHT('0000000' + CAST(n AS VARCHAR(7)), 7)
,Address
FROM @CLIENTS
CROSS APPLY Tally
;WITH Tally (n) AS (
SELECT TOP (@rows) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2)
INSERT INTO @CLIENTS_PHONES
SELECT (id_client * @rows) + n
,id_phone, number
FROM @CLIENTS_PHONES
CROSS APPLY Tally
I get these timing results:
Dwain CPU: 2059 ms, Elapsed: 2091 ms
Barry CPU: 3026 ms, Elapsed: 3058 ms
As you are a member of long and auguste standing, please help me to understand what I did wrong?
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
May 28, 2012 at 7:22 pm
Hello guys, thank you so much for your help, but I get errors (I'm not in my house right now so I cannot be very specific on them), I think this is maybe because I'm using the compact edition, sorry for not specifying this before.
It doesn't matter if it's not efficient or if it takes a while, it's just a for a really basic school project and this is the only thing that doesn't let me finish it 🙁
May 29, 2012 at 9:33 pm
dwain.c (5/28/2012)
RBarryYoung (5/28/2012)
Something like this should work:...
Barry,
I humbly apologize for having to say this but I could only get your solution to work by changing id_phone in the subqueries to id_client, like this:
...
Well! Let me just say this about that: "Oops."
I would also like to humbly submit a more concise version:
DECLARE @CLIENTS TABLE (id_client INT, name VARCHAR(20), address VARCHAR(50))
DECLARE @PHONES TABLE (id_phone INT, description VARCHAR(10))
DECLARE @CLIENTS_PHONES TABLE (id_client INT, id_phone INT, number VARCHAR(20))
INSERT INTO @CLIENTS
SELECT 1, 'Dwain', 'Thailand' UNION ALL SELECT 2, 'Jeff', 'USA'
UNION ALL SELECT 3, 'Tom', 'Germany'
INSERT INTO @PHONES
SELECT 1, 'Home' UNION ALL SELECT 2, 'Office' UNION ALL SELECT 3, 'Cellphone'
INSERT INTO @CLIENTS_PHONES
SELECT 1, 1, '555-1212' UNION ALL SELECT 1, 2, '555-1213'
UNION ALL SELECT 2, 1, '555-1214' UNION ALL SELECT 2, 2, '555-1215' UNION ALL SELECT 2, 3, '555-1216'
SELECT Name, MAX(Address)
,homephone=MAX(CASE description WHEN 'Home' THEN number ELSE NULL END)
,officephone=MAX(CASE description WHEN 'Office' THEN number ELSE NULL END)
,cellphone=MAX(CASE description WHEN 'Cellphone' THEN number ELSE NULL END)
FROM @CLIENTS c
LEFT JOIN @CLIENTS_PHONES cp ON c.id_client = cp.id_client
LEFT JOIN @PHONES p ON p.id_phone = cp.id_phone
GROUP BY Name
...
Nice. I like that one. 🙂
...
If I expand the test harness to 1,000 rows (Jeff would tell me use 1,000,000 but the results already seem apparent), like this:
...
I get these timing results:
Dwain CPU: 2059 ms, Elapsed: 2091 ms
Barry CPU: 3026 ms, Elapsed: 3058 ms
As you are a member of long and auguste standing, please help me to understand what I did wrong?
Well for one thing, you're not kissing my ring with nearly enough sincerity and passion. I expect a puddle of slobber and drool at least an inch deep. 😀
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 29, 2012 at 9:39 pm
Hey Barry!
Glad you don't think I trod too heavily upon your toes on this one.
I've read some of your articles and you do excellent work. I was afraid you might consider me an upstart because I initially missed your response on this thread (thought you had not responded and were ticked off).
Welcome back from your sabbatical (heard that on the watercooler thread)!
Now about kissing your ring and drooling, why don't we just say we'll save that for the next time we go toe to toe. 😛
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
May 29, 2012 at 9:42 pm
porygon3000 (5/28/2012)
It doesn't matter if it's not efficient or if it takes a while, it's just a for a really basic school project and this is the only thing that doesn't let me finish it 🙁
Someone much more experienced that I once asked a question I found very apropos:
If performance doesn't matter, why when given the choice of two queries, will most people always choose the one that performs better?
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 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply