sql join not in table  
Author Message
nakliwala





PostPosted: Tue Nov 06 11:42:08 PST 2007 Top

SQL Server Developer >> sql join not in table

I have a question about comparing two tables and having the output of
all data not in either table. I have tried mulitple ways with the
following the last failed attempt. Thanks in advance.

select distinct a.field1, a.field2, a.field3, a.field4
from table1 a join table2 b
on a.field1=b.field1
and a.field3<>b.field3
and a.field4<>b.field4

Table1
field1 field2 field3 field4
1 a 1 1
1 a 2 1
1 a 3 1
1 a 1 2
1 a 2 2
1 a 3 2
2 b 1 1
2 b 2 1
2 b 3 1
2 b 1 2
2 b 2 2
2 b 3 2
3 b 1 1
3 b 2 1
3 b 3 1
3 b 1 2
3 b 2 2
3 b 3 2



Table2
field1 field2 field3 field4
1 a 1 1
1 a 3 1
1 a 1 2
1 a 3 2
2 b 1 1
2 b 2 1
2 b 2 2
3 c 3 1


The results that I was expected:

field1 field2 field3 field4
1 a 2 1
1 a 2 2
2 b 3 1
2 b 2 1
2 b 3 3
3 a 2 1
3 a 3 1
3 a 1 2
3 a 2 2
3 a 3 2

SQL Server219  
 
 
Russell





PostPosted: Tue Nov 06 11:42:08 PST 2007 Top

SQL Server Developer >> sql join not in table saul,

I don't really understand your rows output versus your narrative. Where did
row "3 a 3 2" come from since it does not exist in either original table?
What columns are you logically joining on? It seems that you want to compare
all columns in a row. Is that correct? So I doubt that the following hits
your target, but maybe it will help you along the way.

select distinct COALESCSE(a.field1, b.field1) AS field1,
COALESCSE(a.field2, b.field2) AS field2,
COALESCSE(a.field3, b.field3) AS field3,
COALESCSE(a.field4, b.field4) AS field4
from table1 a FULL OUTER join table2 b
on a.field1=b.field1
and a.field2 = b.field2
and a.field3=b.field3
and a.field4=b.field4
WHERE a.field1 IS NULL or b.field1 IS NULL

Of course, nothing was said about performance, either. :-)

RLF



>I have a question about comparing two tables and having the output of
> all data not in either table. I have tried mulitple ways with the
> following the last failed attempt. Thanks in advance.
>
> select distinct a.field1, a.field2, a.field3, a.field4
> from table1 a join table2 b
> on a.field1=b.field1
> and a.field3<>b.field3
> and a.field4<>b.field4
>
> Table1
> field1 field2 field3 field4
> 1 a 1 1
> 1 a 2 1
> 1 a 3 1
> 1 a 1 2
> 1 a 2 2
> 1 a 3 2
> 2 b 1 1
> 2 b 2 1
> 2 b 3 1
> 2 b 1 2
> 2 b 2 2
> 2 b 3 2
> 3 b 1 1
> 3 b 2 1
> 3 b 3 1
> 3 b 1 2
> 3 b 2 2
> 3 b 3 2
>
>
>
> Table2
> field1 field2 field3 field4
> 1 a 1 1
> 1 a 3 1
> 1 a 1 2
> 1 a 3 2
> 2 b 1 1
> 2 b 2 1
> 2 b 2 2
> 3 c 3 1
>
>
> The results that I was expected:
>
> field1 field2 field3 field4
> 1 a 2 1
> 1 a 2 2
> 2 b 3 1
> 2 b 2 1
> 2 b 3 3
> 3 a 2 1
> 3 a 3 1
> 3 a 1 2
> 3 a 2 2
> 3 a 3 2
>


 
 
Scott





PostPosted: Tue Nov 06 11:43:21 PST 2007 Top

SQL Server Developer >> sql join not in table One technique - in pseudo-code

select ...
from table1 where not exists (select * from table2 where table1.col =
table2.col ...)
union all
select ...
from table2 where not exists (select * from table1 where table1.col =
table2.col ...)

I'll leave it to you to make the rows distinct.


 
 
s_tx1





PostPosted: Tue Nov 06 12:37:40 PST 2007 Top

SQL Server Developer >> sql join not in table
> saul,
>
> I don't really understand your rows output versus your narrative. Where did
> row "3 a 3 2" come from since it does not exist in either original table?
> What columns are you logically joining on? It seems that you want to compare
> all columns in a row. Is that correct? So I doubt that the following hits
> your target, but maybe it will help you along the way.
>
> select distinct COALESCSE(a.field1, b.field1) AS field1,
> COALESCSE(a.field2, b.field2) AS field2,
> COALESCSE(a.field3, b.field3) AS field3,
> COALESCSE(a.field4, b.field4) AS field4
> from table1 a FULL OUTER join table2 b
> on a.field1=b.field1
> and a.field2 = b.field2
> and a.field3=b.field3
> and a.field4=b.field4
> WHERE a.field1 IS NULL or b.field1 IS NULL
>
> Of course, nothing was said about performance, either. :-)
>
> RLF
>

>

>
>
>
> >I have a question about comparing two tables and having the output of
> > all data not in either table. I have tried mulitple ways with the
> > following the last failed attempt. Thanks in advance.
>
> > select distinct a.field1, a.field2, a.field3, a.field4
> > from table1 a join table2 b
> > on a.field1=b.field1
> > and a.field3<>b.field3
> > and a.field4<>b.field4
>
> > Table1
> > field1 field2 field3 field4
> > 1 a 1 1
> > 1 a 2 1
> > 1 a 3 1
> > 1 a 1 2
> > 1 a 2 2
> > 1 a 3 2
> > 2 b 1 1
> > 2 b 2 1
> > 2 b 3 1
> > 2 b 1 2
> > 2 b 2 2
> > 2 b 3 2
> > 3 b 1 1
> > 3 b 2 1
> > 3 b 3 1
> > 3 b 1 2
> > 3 b 2 2
> > 3 b 3 2
>
> > Table2
> > field1 field2 field3 field4
> > 1 a 1 1
> > 1 a 3 1
> > 1 a 1 2
> > 1 a 3 2
> > 2 b 1 1
> > 2 b 2 1
> > 2 b 2 2
> > 3 c 3 1
>
> > The results that I was expected:
>
> > field1 field2 field3 field4
> > 1 a 2 1
> > 1 a 2 2
> > 2 b 3 1
> > 2 b 2 1
> > 2 b 3 3
> > 3 a 2 1
> > 3 a 3 1
> > 3 a 1 2
> > 3 a 2 2
> > 3 a 3 2- Hide quoted text -
>
> - Show quoted text -

I am sorry....I pasted the table incorrectly. The tables should be as
follows:
table1
field1 field2 field3 field4
1 a 1 1
1 a 2 1
1 a 3 1
1 a 1 2
1 a 2 2
1 a 3 2
2 b 1 1
2 b 2 1
2 b 3 1
2 b 1 2
2 b 2 2
2 b 3 2
3 c 1 1
3 c 2 1
3 c 3 1
3 c 1 2
3 c 2 2
3 c 3 2

table2
field1 field2 field3 field4
1 a 1 1
1 a 3 1
1 a 1 2
1 a 3 2
2 b 1 1
2 b 2 1
2 b 2 2
3 c 3 1

results
field1 field2 field3 field4
1 a 2 1
1 a 2 2
2 b 3 1
2 b 2 1
2 b 3 3
3 c 2 1
3 c 3 1
3 c 1 2
3 c 2 2
3 c 3 2


 
 
s_tx1





PostPosted: Tue Nov 06 12:49:25 PST 2007 Top

SQL Server Developer >> sql join not in table
> saul,
>
> I don't really understand your rows output versus your narrative. Where did
> row "3 a 3 2" come from since it does not exist in either original table?
> What columns are you logically joining on? It seems that you want to compare
> all columns in a row. Is that correct? So I doubt that the following hits
> your target, but maybe it will help you along the way.
>
> select distinct COALESCSE(a.field1, b.field1) AS field1,
> COALESCSE(a.field2, b.field2) AS field2,
> COALESCSE(a.field3, b.field3) AS field3,
> COALESCSE(a.field4, b.field4) AS field4
> from table1 a FULL OUTER join table2 b
> on a.field1=b.field1
> and a.field2 = b.field2
> and a.field3=b.field3
> and a.field4=b.field4
> WHERE a.field1 IS NULL or b.field1 IS NULL
>
> Of course, nothing was said about performance, either. :-)
>
> RLF
>

>

>
>
>
> >I have a question about comparing two tables and having the output of
> > all data not in either table. I have tried mulitple ways with the
> > following the last failed attempt. Thanks in advance.
>
> > select distinct a.field1, a.field2, a.field3, a.field4
> > from table1 a join table2 b
> > on a.field1=b.field1
> > and a.field3<>b.field3
> > and a.field4<>b.field4
>
> > Table1
> > field1 field2 field3 field4
> > 1 a 1 1
> > 1 a 2 1
> > 1 a 3 1
> > 1 a 1 2
> > 1 a 2 2
> > 1 a 3 2
> > 2 b 1 1
> > 2 b 2 1
> > 2 b 3 1
> > 2 b 1 2
> > 2 b 2 2
> > 2 b 3 2
> > 3 b 1 1
> > 3 b 2 1
> > 3 b 3 1
> > 3 b 1 2
> > 3 b 2 2
> > 3 b 3 2
>
> > Table2
> > field1 field2 field3 field4
> > 1 a 1 1
> > 1 a 3 1
> > 1 a 1 2
> > 1 a 3 2
> > 2 b 1 1
> > 2 b 2 1
> > 2 b 2 2
> > 3 c 3 1
>
> > The results that I was expected:
>
> > field1 field2 field3 field4
> > 1 a 2 1
> > 1 a 2 2
> > 2 b 3 1
> > 2 b 2 1
> > 2 b 3 3
> > 3 a 2 1
> > 3 a 3 1
> > 3 a 1 2
> > 3 a 2 2
> > 3 a 3 2- Hide quoted text -
>
> - Show quoted text -

And I wanted to thank you that query worked perfect.


 
 
Hugo





PostPosted: Wed Nov 07 15:14:04 PST 2007 Top

SQL Server Developer >> sql join not in table

>I have a question about comparing two tables and having the output of
>all data not in either table. I have tried mulitple ways with the
>following the last failed attempt. Thanks in advance.

Hi s_tx1,

If you are on SQL Server 2005, you can also use this method:

SELECT *
FROM Table1
UNION ALL
SELECT *
FROM Table2
EXCEPT
(SELECT *
FROM Table1
INTERSECT
SELECT *
FROM Table2);

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis