Multiple Columns Not In SQL View

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 2008 Forums

Transact-SQL (2008)

Multiple Columns Not In SQL View

Author

Topic

wldodds
Starting Member

20 Posts

Posted - 2013-08-27 : 17:58:33

I have 2 SQL Views which contain the following

select PONumber, ItemNumber from viewa

select PONumber, ItemNumber from viewb

I would like to show all records from viewa and viewb where PONumber and ItemNumber are not in both viewa and viewb. Originally I was going to do a Union but I have more columns in viewa and viewb that I need to show and the number of columns are not the same. I'm thinking inner join or outer join?

Any help is greatly appreciated.

Thanks,

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2013-08-27 : 18:19:46

Let me get this straight so I understand what you want. If i have the following sample set:

TableA
--------------
Number
--------------
1
2
3
4
5
6

TableB
--------------
Number
--------------
4
5
6
7
8
9

You want to display the following (records from tableA and tableB that don't exist in both):

Number
--------------
1
2
3
7
8
9

This excluded rows 4,5,6 because they exist in both. Is this the correct result?

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-08-27 : 19:31:20

[code]select
coalesce(a.Number, b.Number) Number
from
TableA a
FULL OUTER JOIN
TableB b
on a.Number = b.Number
where (
a.Number is NULL
OR b.Number is NULL)[/code]This does assume that the columns are not nullable. If they are then you'd need to check for that.

=================================================
The cure for anything is salt water -- sweat, tears, or the sea. -Isak Dinesen

wldodds
Starting Member

20 Posts

Posted - 2013-08-27 : 21:59:21

Lazerath,

You are correct, however I have 2 columns so it would be Number and Item as the 2 columns and I need the records where both columns don't exist in either table so the result set shows the unique records from each table (i.e. no duplicates).

Bustaz, coalesce works for 1 column but how do I use this for 2 columns Number and Item in both TableA and TableB?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-28 : 03:03:04

if you want just columns from both views and if columns are different do you want them as two different resultsets? otherwise it doesnt make any sense to merge two different resultsets

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

ShivaKrishna
Starting Member

20 Posts

Posted - 2013-08-28 : 04:55:53

select
coalesce(a.Number, b.Number) Number
from
TableA a
FULL OUTER JOIN
TableB b
on a.Number = b.Number
minus (select (a.Number) Number
from
TableA a
Inner join
TableB b
on a.Number = b.Number)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources