SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Query to consoldate tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 07/24/2007 :  07:55:25  Show Profile  Reply with Quote
I have two tables, they have most fields in common however there are fields in table1 which are not in table2 and vice versa.

Is there a query I can use to get all records from one table and all records from the other.

I want the result set to contain all fields from both tables.

What i'm looking for really is a UNION ALL type result but using tables with different fields.

Thanks

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 07/24/2007 :  08:01:15  Show Profile  Reply with Quote
If both table have matching records, meaning records in table1 exists in table2 and vice versa, you can just use INNER JOIN

SELECT t1.col1, t1.col2, t1.col3, t2.col4, t2.col5, t1.col6
FROM   table1 t1 INNER JOIN table2 t2
ON     t1.pk = t2.pk



KH
Time is always against us

Go to Top of Page

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 07/24/2007 :  09:41:15  Show Profile  Reply with Quote
The records in T1 dont relate in any way to T2, what I mean is there is no primary/foreign key relationship between them
Go to Top of Page

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 07/24/2007 :  09:45:59  Show Profile  Reply with Quote
Example

Table1

CDID Make Model Engine

2087 Ford Mondeo 1.8
7765 Audi A4 2.0
7676 BMW 318i 1.9

Table 2

Make Model Derivative Customer

Audi A6 1.8 SE 5dr SMITH
Cit C5 2.0S JONES

Desired Results

CDID Make Model Engine Derivative Customer
2087 Ford Mondeo 1.8 NULL NULL
7765 Audi A4 2.0 NULL NULL
7676 BMW 318i 1.9 NULL NULL
NULL Audi A6 NULL 1.8 SE 5Dr SMITH
NULL CIT C5 NULL 2.0S JONES

Thanks
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 07/24/2007 :  09:47:16  Show Profile  Reply with Quote
you just want a result set that combines all records of both table ?
SELECT col1, col2, col3, col4 = NULL, col5 = NULL, col6
FROM   table1

UNION ALL

SELECT col1 = NULL, col2 = NULL, col3 = NULL, col4, col5, col6 = NULL
FROM   table2



KH
Time is always against us

Go to Top of Page

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 07/24/2007 :  11:23:42  Show Profile  Reply with Quote
Thats it - thanks. Didn't realise you could put blanks in the select statement to satisfy the requirements of UNION
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000