subquery, join ???

[m0nkey]

Hi All,
THis one has stumped me a bit...I have a table that has customer info in it and i have the address in an address table that has 3 types of addresses contained within: Current, Billing, and Installed...
I have never had to create this report and thus am a bit stumped...I want to return in one result set the install address and the billing address...I am a bit stumped on how to approach this...
i would like the application_id and both the addresses that coincide:
app_id | install_address_line1 | install_city | bill_address_line1 | bill_city | .....

but currently in the table i have just fk_app_id, address_line1, city, state, zip, address_type

Any ideas???
thanks in advance...
mark

 

[NathanGriffiths]

How about;

SELECT 
	i.fk_app_id AS app_id,
	i.address_line_1 AS install_address_line1, 
	i.city AS install_city,
	b.address_line_1 AS bill_address_line_1,
	b.city AS bill_city
FROM 
	(
	SELECT 
		fk_app_id, 
		address_line_1, 
		city 
	FROM 
		AddressTable 
	WHERE 
		address_type = 'I'
	) AS i
JOIN 
	(
	SELECT 
		fk_app_id, 
		address_line_1, 
		city 
	FROM 
		AddressTable 
	WHERE 
	address_type = 'B'
	) AS b 
	ON i.fk_app_id = b.fk_app_id

Nathan

----------------------------------------
Turn your face to the sun and the shadows fall behind you - Maori proverb
----------------------------------------

 

[m0nkey]

Awesome....Thanks a million....i learned a new query...thanks...