Combine two lists - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Combine two lists

Author

Topic

SergioM
Posting Yak Master

170 Posts

Posted - 2014-03-10 : 16:37:45

I have three tables, Orders, ShippingAddress & BillingAddress. ShippingAddress has OrderID, FirstName, LastName & ZipCode. BillingAddress also has OrderID, FirstName, LastName & ZipCode.

I want to make it one long list where the order ID appears twice rather than make it an inner join where FirstName, LastName & ZipCode appear twice. It should be easy, but I can't think of how to do it.

SELECT 
	sa.[id]
	,sa.[FirstName]
	,sa.[LastName]
	,LEFT([sa].[PostalCode],5) as 'zip'
FROM [SC].[CGB].[ShippingAddress ] sa
INNER JOIN [SC].[CGB].[BillingAddress] ba
	ON sa.OrderID = ba.OrderID
Where sa.OrderID IN (SELECT [ID]
FROM [SC].[CGB].[Orders]
WHERE ORDERSTATUS='1'

-Sergio
I use Microsoft SQL 2008

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-03-10 : 17:18:03

Looks like what you want is UNION ALL (or UNION if you want to avoid duplicates)

select id, firstname, lastname, LEFT([PostalCode],5) as 'zip' from BillingAddress
union all
select id, firstname, lastname, LEFT([PostalCode],5) as 'zip' from ShippingAddress

SergioM
Posting Yak Master

170 Posts

Posted - 2014-03-11 : 11:26:13

heh, that was easy enough. I've never used union all, but it makes sense. Thanks

-Sergio
I use Microsoft SQL 2008

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources