Need Help with a query - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Need Help with a query

Author

Topic

hismightiness
Posting Yak Master

164 Posts

Posted - 2005-05-18 : 13:15:36

I have 3 tables that are all identical in design. Their columns are as follows:

Col001 nvarchar 50 (itemid)
Col002 nvarchar 255 (description)
Col003
Col004
Col005
Col006
Col007

I am only concerned with columns 1 & 2. Lets say the table names are Table1, Table2, and Table3. I want to perform a query that will list all items not contained in all 3 tables. They can be contained in 1 or 2, but not all 3.

I have tried a few things to get this done, but I am afraid that either I am just missing something obvious, or this is something too advanced for me. Can someone please assist me with this query statement?

- - - -
- Will -
- - - -
http://www.servicerank.com/

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2005-05-18 : 13:32:24

This query will probably suck performance wise but it may do the trick.


SELECT a.col001, a.col002
FROM (SELECT Table1.col001 AS col001, Table1.col002
      FROM Table1
      UNION
      SELECT Table2.col001 AS col001, Table2.col002
      FROM Table2
      UNION
      SELECT Table3.col001 AS col001, Table3.col002
      FROM Table3) a LEFT OUTER JOIN Table1 ON (a.col001 = Table1.col001 AND a.col002 = Table1.col002)
                     LEFT OUTER JOIN Table2 ON (a.col001 = Table2.col001 AND a.col002 = Table2.col002)
                     LEFT OUTER JOIN Table3 ON (a.col001 = Table3.col001 AND a.col002 = Table3.col001)
WHERE Table1.col001 IS NULL OR Table2.col001 IS NULL OR Table3.col001 IS NULL

You should normalize your design and make 1 table and add an additional type column to it. If you do this then you can get take your 3 tables and move it into the single table.

Dustin Michaels

hismightiness
Posting Yak Master

164 Posts

Posted - 2005-05-18 : 13:44:39

This isn't a permanent thing. It is only a reference point. Once the query is properly performed, I can thankfully trash the data.

Thanks a bunch! Your query did the trick!!!

- - - -
- Will -
- - - -
http://www.servicerank.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources