Find Column that exixts in 1 Table

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)

Find Column that exixts in 1 Table

Author

Topic

DestinyJack
Starting Member

22 Posts

Posted - 2005-08-24 : 05:55:35

I have 2 tables as following:

TableA TableB
====== ======
RecNum RecNum
Column1 Column1
Column2 Column2
Column3 Column3

If I need to select column1, Column2 or Column3 that only exists in either TableA or TableB, how should I write the select statement?

Example:
if any column in TableA exists only in TableA, show the row.
if any column in TableB exists only in TableB, show the row.
if any column in exists in both tableA and TableB, do not show the row.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-24 : 06:06:08

Do you want to show the records that dont exists in both the tables?
Can you give us some sample data and the result you want?

Madhivanan

Failing to plan is Planning to fail

DestinyJack
Starting Member

22 Posts

Posted - 2005-08-24 : 06:09:49

Yes, I want to show the records that don't exists in both the tables.

TableA
======
Column1 Column2 Column3
Apple Orange Fruit
Meat Fish Seafood

TableB
======
Column1 Column2 Column3
Car Ship Transport
Meat Fish Seafood

So the result will show:
Car Ship Transport
Apple Orange Fruit

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-08-24 : 06:11:59

[code]
select t1.*, t2.*
from tableA t1 left join TableB t2 on t1.Column1 = t2.Column1 and t1.Column2 = t2.Column2 and t1.Column3 = t2.Column3
where t2.Column1 is null and
t2.Column2 is null and
t2.Column3 is null
[/code]

Go with the flow & have fun! Else fight the flow

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-24 : 06:20:53

Try this

Select * from(
Select t1.*,t2.column1 as col1, t2.column2 as col2, t2.column3 as col3 from table1 T1
left join table2 T2 on t1.column1=t2.column1 and t1.column2=t2.column2 and t1.column3=t2.column3) T
where col1 is null

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-24 : 06:22:31

Mladen, When I posted I didnot see your reply
Yours is more clearer than mine

Madhivanan

Failing to plan is Planning to fail

DestinyJack
Starting Member

22 Posts

Posted - 2005-08-24 : 06:42:00

The result I need will contains rows that doesn't exists in both table, meaning if it exists in TableA and not TableA, or if it exists in TableB but not in TableA.

select t1.*, t2.*
from tableA t1 left join TableB t2 on t1.Column1 = t2.Column1 and t1.Column2 = t2.Column2 and t1.Column3 = t2.Column3
where t2.Column1 is null and
t2.Column2 is null and
t2.Column3 is null

The statement above will only show records that exists in TableA but not in TableB. It didn't show records that exists in TableB but not in tableA. But it give me some clues for the solution:

select t1.*, t2.*
from tableA t1 full outer join TableB t2 on t1.Column1 = t2.Column1 and t1.Column2 = t2.Column2 and t1.Column3 = t2.Column3
where
(t1.Column1 is null and
t1.Column2 is null and
t1.Column3 is null)
or
(t2.Column1 is null and
t2.Column2 is null and
t2.Column3 is null)

This is what I modified and it give me the result I want, but in the following format:
Column1 Column2 Column3 Column1 Column2 Column3
NULL NULL NULL Car Plane Transport
Apple Orange Fruit NULL NULL NULL

Is there anyway to remove all the column with "NULL"?

Thanks a lot for the helps.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-24 : 06:55:26

If you want to remove Null and show empty space then specify the columns in Select query as
isnull(column,'')

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources