x

Join identical tables

I am using Sql 2k5. I have 2 tables thats are identical in all aspects except the data. I need to join the 2 tables using the same WHERE condition. How do i do it without using a UNION?

For example, the 2 tables are goodCustomer and badCustomer.

I would like to join the data in these 2 tables using a WHERE condition like

WHERE login_Date > '2009-01-01' or DealNumber = 0

How would the query change if the data is mutually exclusive and if it isnt.

Any help is appreciated.

Thanks

more ▼

asked Dec 02 '09 at 03:05 AM in Default

Bala gravatar image

Bala
42 3 3 3

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Why don't you wan't to use UNION? I am not sure what you want to accomplish, but have you tried INTERSECT or EXCEPT? I give you some samples for you to try.

This select will return every record from table1 where there are matching records in table2:

SELECT someCol1, someCol2 FROM table1 
WHERE login_Date > '2009-01-01' or DealNumber = 0
INTERSECT 
SELECT someCol1, someCol2 FROM table2 
WHERE login_Date > '2009-01-01' or DealNumber = 0

This select will return every record from table1 where there are no matching records in table2:

SELECT someCol1, someCol2 FROM table1 
WHERE login_Date > '2009-01-01' or DealNumber = 0
EXCEPT
SELECT someCol1, someCol2 FROM table2 
WHERE login_Date > '2009-01-01' or DealNumber = 0

Another solution may be FULL OUTER JOIN, the following sample will give you every record from table1 and table2 that matches your where clause. If there records in table1 that does not have any records in table2, you will get NULL for the table2 columns, etc. :

SELECT * FROM table1 AS t1 FULL OUTER JOIN table2 as t2 ON t1.yourPrimaryKey = t2.yourPrimaryKey WHERE (t1.login_Date > '2009-01-01' OR t1.DealNumber = 0 OR t1.yourPrimaryKey IS NULL) AND (t2.login_Date > '2009-01-01' OR t2.DealNumber = 0 OR t2.yourPrimaryKey IS NULL) 
more ▼

answered Dec 02 '09 at 04:57 AM

Håkan Winther gravatar image

Håkan Winther
15.5k 33 37 48

(comments are locked)
10|1200 characters needed characters left

In addition to the other suggestions, since you're trying to combine the data from the two tables, you should probably be using UNION ALL. With that you won't see the aggregation functions that are normally associated with a UNION, so the performance should be very acceptable.

more ▼

answered Dec 02 '09 at 10:26 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
91k 19 21 74

(comments are locked)
10|1200 characters needed characters left

Can I ask why you do not want to use a UNION? It seems that UNION, or as Grant suggested UNION ALL is exactly what you want if I understand the rest of the question correctly.

If the problem is that you do not want two separate sets of where conditions then you can use a subquery or CTE. For instance you could use something like:

With CustomerCTE as ( select * from goodCustomer UNION ALL select * from badCustomer)select * from CustomerCTE WHERE login_Date > '2009-01-01' or DealNumber = 0 
more ▼

answered Dec 02 '09 at 02:00 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 19 22 32

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1936
x103

asked: Dec 02 '09 at 03:05 AM

Seen: 2278 times

Last Updated: Dec 02 '09 at 01:47 PM