MS-SQL query problem

R

Raithlin

Executive Member
Joined
Jan 4, 2005
Messages
5,060
Reaction score
33
Location
Centurion
Hi all.

I'm struggling with an SQL query from a particular table. The table is normalised, containing addresses. For the sake of argument, let's call the Table Address.
Address has within it a few fields: AddressID, Line1, Line2, Line3 and AddressType. AddressID is the PK. AddressType is one of several codes, translating to either residential, business, postal, etc.

I need to return a single row that has residential, postal and business address details. I have tried self-joins, but I get 3 rows - each with unique data.

My question is this: How do I get those rows into one. All the data I want is there - but it's separated into 3 rows, and surrounded by NULLs.

I've spent the afternoon searching the web to no avail. (If I see a link to lmgtfy.com in the replies, I'll hunt you down and kill you :D) I've tried COALESCE, I've tried CASE - I don't want to do 3 selects into a temp table if I can help it. I've done this before, just can't remember how.

Oh - code is below, in case you want to wade through it. :p

Code: 
SELECT DISTINCT
	CPh.ADDRESS_LINE1 AS 'CurrBusAddr1',
	CPh.ADDRESS_LINE2 AS 'CurrBusAddr2',
	CPh.ADDRESS_LINE3 AS 'CurrBusAddr3',
	CPh.POSTAL AS 'CurrBusAddrCode',
	CPh.Suburb AS 'CurrBusAddrSuburb',
	PPh.ADDRESS_LINE1 AS 'PrevBusAddr1',
	PPh.ADDRESS_LINE2 AS 'PrevBusAddr2',
	PPh.ADDRESS_LINE3 AS 'PrevBusAddr3',
	PPh.POSTAL AS 'PrevBusAddrCode',
	PPh.Suburb AS 'PrevBusAddrSuburb',
	CPo.ADDRESS_LINE1 AS 'PostAddr1',
	CPo.ADDRESS_LINE2 AS 'PostAddr2',
	CPo.ADDRESS_LINE3 AS 'PostAddr3',
	CPo.POSTAL AS 'PostAddrCode',
	CPo.Suburb AS 'PostAddrSuburb'
FROM dbo.PROSPECT PR
INNER JOIN dbo.PARTNER_ADDR PA ON PA.PARTNER_ID = PR.PARTNER_ID AND PA.CONTACT_PERSON_ID IS NULL
LEFT OUTER JOIN dbo.ADDRESS CPh ON CPh.ADDRESS_ID = PA.ADDRESS_ID AND CPh.ADDR_TYPE_CD = 'Bus'
LEFT OUTER JOIN dbo.ADDRESS PPh ON PPh.ADDRESS_ID = PA.ADDRESS_ID AND PPh.ADDR_TYPE_CD = 'Res'
LEFT OUTER JOIN dbo.ADDRESS CPo ON CPo.ADDRESS_ID = PA.ADDRESS_ID AND CPo.ADDR_TYPE_CD = 'Pos'
WHERE PR.PROSPECT_ID = 120

At the moment it returns this:
Code: 
CurrBusAddr1	CurrBusAddr2	CurrBusAddr3	CurrBusCode	CurrBusSuburb	PrevBusAddr1	PrevBusAddr2	PrevBusAddr3	PrevBusCode	PrevBusSuburb	PostAddr1	PostAddr2	PostAddr3	PostCode	PostSuburb
NULL		NULL		NULL		NULL		NULL		NULL		NULL		NULL		NULL		NULL		NULL		NULL		NULL		NULL		NULL
NULL		NULL		NULL		NULL		NULL		NULL		NULL		NULL		NULL		NULL		PO BOX 2	NULL		1483		JET PARK
NULL		NULL		NULL		NULL		NULL		1 PREV ROAD	PREV PARK	PREVIOUS 3	2193		GREENSIDE	NULL		NULL		NULL		NULL		NULL
UNIT A2		JHB		1459		JET PARK	NULL		NULL		NULL		NULL		NULL		NULL		NULL		NULL		NULL		NULL

Thanks in advance.
 
Last edited:
Instead of doing views (with which can can emulate cross-tabs), why not just do cross-joins in one nice, big query?
 
I'm not near my SQL so can't really fool around, but maybe try a "GROUP BY" and "HAVING" somewhere instead of distinct?

otherwise, a surefire way which i don't find very elegant is using some form of nested/inner select :

select
'col1' = (select top 1 column from [adress] a1 where a1.adress_id = p.adress_id and column is not null) ,
'col2' = (select top 1 column from [adress] a2 where a2.adress_id = p.adress_id and column is not null) ,
..

FROM Prospect p
WHERE p.prospect_ID = 120

(again i know it's definitely not very efficient, but you sure will get your one row ;))

OR

you can attempt to move it down to the FROM i.e.

SELECT
bus.column,
prev.column
FROM Prospect P
LEFT/iNNEr join (Select top 1/distinct [columns] From Adress where x is not null) bus
ON p.adress_id = bus.adres_id
LEFT/iNNEr join (Select top 1/distinct [columns] From Adress where x is not null) prev
ON p.adress_id = bprev.adres_id
 
Last edited:
diabolus said:
I'm not near my SQL so can't really fool around, but maybe try a "GROUP BY" and "HAVING" somewhere instead of distinct?
Click to expand...

GROUP BY and HAVING won't "wrap" query resulty.
Consider:
tblAddress
ID_Type_Line1____Line2
1__RES__"1_LINE1" "1_LINE2"
2__BUS__"2_LINE1" "2_LINE2"
3__POS__"3_LINE1" "3_LINE2"

What Raithlin wants is
RES_Line1 RES_Line2 BUS_Line1 BUS_Line2 POS_Line1 POS_Line2
"1_LINE1"_"1_LINE2"_"2_LINE1"_"2_LINE2"_"3_LINE1"__"3_LINE2"

You can't do they using GROUP BY, because you're limited by you columns in the SELECT and normally GROUP BY is used when aggregating a column(s).

I'm still convinced you need to use PIVOT, haven't worked with it enough to give an example off the top of my head.
 
I was thinking about what diabolous suggested and I think he actually may be correct. Because to use PIVOT you also have to aggregate the pivoted columns, so it may not be the best approach.

If you change the query to include the PROSPECT_ID, you can then GROUP BY this and it "squash" your results into one row.

Check this out:
Code: 
SELECT DISTINCT
	PR.PROSPECT_ID
	MAX(CPh.ADDRESS_LINE1) AS 'CurrBusAddr1',
	MAX(CPh.ADDRESS_LINE2) AS 'CurrBusAddr2',
 	MAX(CPh.ADDRESS_LINE3) AS 'CurrBusAddr3',
	MAX(CPh.POSTAL) AS 'CurrBusAddrCode',
	MAX(CPh.Suburb) AS 'CurrBusAddrSuburb',
	MAX(PPh.ADDRESS_LINE1) AS 'PrevBusAddr1',
	MAX(PPh.ADDRESS_LINE2) AS 'PrevBusAddr2',
	MAX(PPh.ADDRESS_LINE3) AS 'PrevBusAddr3',
	MAX(PPh.POSTAL) AS 'PrevBusAddrCode',
	MAX(PPh.Suburb) AS 'PrevBusAddrSuburb',
	MAX(CPo.ADDRESS_LINE1) AS 'PostAddr1',
	MAX(CPo.ADDRESS_LINE2) AS 'PostAddr2',
	MAX(CPo.ADDRESS_LINE3) AS 'PostAddr3',
	MAX(CPo.POSTAL) AS 'PostAddrCode',
	MAX(CPo.Suburb) AS 'PostAddrSuburb'
FROM dbo.PROSPECT PR
INNER JOIN dbo.PARTNER_ADDR PA ON PA.PARTNER_ID = PR.PARTNER_ID AND PA.CONTACT_PERSON_ID IS NULL
LEFT OUTER JOIN dbo.ADDRESS CPh ON CPh.ADDRESS_ID = PA.ADDRESS_ID AND CPh.ADDR_TYPE_CD = 'Bus'
LEFT OUTER JOIN dbo.ADDRESS PPh ON PPh.ADDRESS_ID = PA.ADDRESS_ID AND PPh.ADDR_TYPE_CD = 'Res'
LEFT OUTER JOIN dbo.ADDRESS CPo ON CPo.ADDRESS_ID = PA.ADDRESS_ID AND CPo.ADDR_TYPE_CD = 'Pos'
WHERE PR.PROSPECT_ID = 120
GROUP BY PR.PROSPECT_ID
 
Thanks for the responses.

@Jabberwocky - exactly what I don't want to do.

I'm not going near PIVOT - saw it yesterday, but it doesn't seem to achieve the effect I'm looking for (all the examples I saw involved numbers, not strings). Dequadin (helped by Diabolus), I'll give grouping a try, as you suggested.

Farlig, what exactly do you propose? I've tried a few join types already...
 
dequadin said:
If you change the query to include the PROSPECT_ID, you can then GROUP BY this and it "squash" your results into one row.
Click to expand...

Yea that will work nicely , i forgot to mention the "MAX" aggregation. Since any "value" will always be larger than "null" you can eliminate it via that .

My test query would be:
SELECT
id, max(col1),max(col2),max(col3)
FROM
(
SELECT
'id'= 1,'col1' = null,'col2' = 'myval2', 'col3' = null
union
SELECT
1,'myval1',null,null
union
SELECT
1,null,null,'myval3'
) sub


GROUP BY id
Click to expand...

which would turn

1 NULL NULL myval3
1 NULL myval2 NULL
1 myval1 NULL NULL
Click to expand...

into

1 myval1 myval2 myval3
Click to expand...
 
Last edited:
Oh man, seems like me and diabolus had the same idea... :o Well, here's my finding anyways (I tested it on a small, simulated database):

Code: 
SELECT
	MAX(PROSPECT_ID),
	MAX(BUS_LINE1) as BUS_LINE1,
	MAX(BUS_LINE2) as BUS_LINE2,
	MAX(BUS_LINE3) as BUS_LINE3,
	MAX(BUS_SUBURB) as BUS_SUBURB,
	MAX(BUS_POSTAL) as BUS_POSTAL,
	MAX(RES_LINE1) as RES_LINE1,
	MAX(RES_LINE2) as RES_LINE2,
	MAX(RES_LINE3) as RES_LINE3,
	MAX(RES_SUBURB) as RES_SUBURB,
	MAX(RES_POSTAL) as RES_POSTAL,
	MAX(POS_LINE1) as POS_LINE1,
	MAX(POS_LINE2) as POS_LINE2,
	MAX(POS_LINE3) as POS_LINE3,
	MAX(POS_SUBURB) as POS_SUBURB,
	MAX(POS_POSTAL) as POS_POSTAL
FROM
(
	SELECT
		PROSPECT_ID,
		ADDRESS_LINE1 AS BUS_LINE1,
		ADDRESS_LINE2 AS BUS_LINE2,
		ADDRESS_LINE3 AS BUS_LINE3,
		POSTAL AS BUS_POSTAL,
		SUBURB AS BUS_SUBURB,
		NULL AS RES_LINE1,
		NULL AS RES_LINE2,
		NULL AS RES_LINE3,
		NULL AS RES_POSTAL,
		NULL AS RES_SUBURB,
		NULL AS POS_LINE1,
		NULL AS POS_LINE2,
		NULL AS POS_LINE3,
		NULL AS POS_POSTAL,
		NULL AS POS_SUBURB
	FROM dbo.PROSPECT P
		INNER JOIN dbo.PARTNER_ADDR PA ON P.PARTNER_ID = PA.PARTNER_ID AND PA.CONTACT_PERSON_ID IS NULL
		INNER JOIN dbo.ADDRESS A ON PA.ADDRESS_ID = A.ADDRESS_ID AND A.ADDR_TYPE_CD = 'Bus'

	UNION ALL

	SELECT
		PROSPECT_ID,
		NULL AS BUS_LINE1,
		NULL AS BUS_LINE2,
		NULL AS BUS_LINE3,
		NULL AS BUS_POSTAL,
		NULL AS BUS_SUBURB,
		ADDRESS_LINE1 AS RES_LINE1,
		ADDRESS_LINE2 AS RES_LINE2,
		ADDRESS_LINE3 AS RES_LINE3,
		POSTAL AS RES_POSTAL,
		SUBURB AS RES_SUBURB,
		NULL AS POS_LINE1,
		NULL AS POS_LINE2,
		NULL AS POS_LINE3,
		NULL AS POS_POSTAL,
		NULL AS POS_SUBURB
	FROM dbo.PROSPECT P
		INNER JOIN dbo.PARTNER_ADDR PA ON P.PARTNER_ID = PA.PARTNER_ID AND PA.CONTACT_PERSON_ID IS NULL
		INNER JOIN dbo.ADDRESS A ON PA.ADDRESS_ID = A.ADDRESS_ID AND A.ADDR_TYPE_CD = 'Res'

	UNION ALL

	SELECT
		PROSPECT_ID,
		NULL AS BUS_LINE1,
		NULL AS BUS_LINE2,
		NULL AS BUS_LINE3,
		NULL AS BUS_POSTAL,
		NULL AS BUS_SUBURB,
		NULL AS RES_LINE1,
		NULL AS RES_LINE2,
		NULL AS RES_LINE3,
		NULL AS RES_POSTAL,
		NULL AS RES_SUBURB,
		ADDRESS_LINE1 AS POS_LINE1,
		ADDRESS_LINE2 AS POS_LINE2,
		ADDRESS_LINE3 AS POS_LINE3,
		POSTAL AS POS_POSTAL,
		SUBURB AS POS_SUBURB
	FROM dbo.PROSPECT P
		INNER JOIN dbo.PARTNER_ADDR PA ON P.PARTNER_ID = PA.PARTNER_ID AND PA.CONTACT_PERSON_ID IS NULL
		INNER JOIN dbo.ADDRESS A ON PA.ADDRESS_ID = A.ADDRESS_ID AND A.ADDR_TYPE_CD = 'Pos'
) ADDRESSES
GROUP BY PROSPECT_ID
 
MAX works a treat (on one select, too) - but won't all those max functions have a performance hit on the server?
 
Raithlin said:
MAX works a treat (on one select, too) - but won't all those max functions have a performance hit on the server?
Click to expand...

It might... Best to test it out with a crap-load of data and see what happens. If the query takes anything more than a few milliseconds to execute, you should consider an alternative.
 
I ran a execution plan on it, and the highest cost was the remote query call (I call 2 servers for the results - not my design). I'm gonna go with it.

For the record, I ended up with a select max on the OP - no group by, no ID displayed - works a bomb. Thanks to all involved.
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X