Union / Join ? - 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

Union / Join ?

Author

Topic

mark.s
Starting Member

3 Posts

Posted - 2007-08-07 : 04:35:42

I am trying to merge the selected contents of 2 tables by using a date field in both tables.

For example, in the AdventureWorks database of VS2005, I would like 3 columns: loginID, modifieddate and addressline1. Where the row has come from the Employee table, the address field should be blank, so its not really a join that I want (at least I dont think so).

This query only returns 2 colums:
select loginid, modifieddate from HumanResources.Employee union all select addressLine1, modifiedDate from Person.Address

Is this possible, or do I need an additional common column that can be inner joined on the 2 tables?
Can anyone help?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-07 : 04:46:15

You can use UNION. But in this case, union 2 totally unrelated table gives result that is meaningless.

SELECT 	LoginID, ModifiedDate, '' AS AddressLine1
FROM 	HumanResources.Employee 

UNION ALL 

SELECT 	'' AS LoginID, ModifiedDate, AddressLine1
FROM 	Person.Address

KH
[spoiler]Time is always against us[/spoiler]

mark.s
Starting Member

3 Posts

Posted - 2007-08-07 : 05:17:14

Brilliant thanks!

Yes I know it may seem rather pointless for this example, but this was just an example of 2 tables with date fields.

Thanks anyway.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources