Very Computer

Filtering criteria should be based on columns i, fname,
lname.

Basically, elminate all records
where tab1.i = tab2.i and tab1.fname = tab2.fname and
tab1.lname = tab2.lname. Is there anything like a minus
operator.

i       fname   lname   another_column
1       Fname1  Lname1  asdfj
2       Fname2  Lname2  NULL
3       Fname3  Lname3  lsadfj
4       Fname4  Lname4  NULL
5       Fname5  Lname5  NULL

i       fname   lname   another_column
1       Fname1  Lname1  NULL
2       Fname2  Lname2  NULL
3       Fname3  Lname3  NULL
6       Fname6  Lname6  NULL
7       Fname7  Lname7  NULL

You can use the sample script to create the tables as
temporary tables.
set nocount on
create table #tmp1 (
        i                               int,
        fname                   char(20),
        lname                   char(20),
        another_column  char(20) null
)
create table #tmp2 (
        i               int,
        fname   char(20),
        lname   char(20),
        another_column  char(20) null
)

insert into #tmp1
select  1, 'Fname1', 'Lname1', 'asdfj'

insert into #tmp1
select  2, 'Fname2', 'Lname2', null

insert into #tmp1
select  3, 'Fname3', 'Lname3', 'lsadfj'

insert into #tmp1
select  4, 'Fname4', 'Lname4', null

insert into #tmp1
select  5, 'Fname5', 'Lname5', null

insert into #tmp2
select  1, 'Fname1', 'Lname1', null

insert into #tmp2
select  2, 'Fname2', 'Lname2', null

insert into #tmp2
select  3, 'Fname3', 'Lname3', null

insert into #tmp2
select  6, 'Fname6', 'Lname6', null

insert into #tmp2
select  7, 'Fname7', 'Lname7', null

select * from #tmp1
select * from #tmp2

drop table #tmp1
drop table #tmp2
set nocount off

Please try to help me.

Thanks.

select
 *
from
 #tmp1 t1 left  join #tmp2 t2 on
  t1.fname = t2.fname
  and t1.lname = t2.lname
where
 t2.fname IS NULL

UNION

select
 *
from
 #tmp2 t2 left  join #tmp1 t1 on
  t2.fname = t1.fname
  and t2.lname = t1.lname
where
 t1.fname IS NULL

--

Brian Moran
SQL Server MVP
SQL Server Magazine Columnist

Quote:> I have two tables with the following data. I need rows
> where i = 4, 5, 6 and 7. Rows 1, 2, 3 should be eliminated
> as they are available in both the tables. Can this be done
> within a select statement using subqueries or do I need to
> use cursor.

> Filtering criteria should be based on columns i, fname,
> lname.

> Basically, elminate all records
> where tab1.i = tab2.i and tab1.fname = tab2.fname and
> tab1.lname = tab2.lname. Is there anything like a minus
> operator.

> i fname lname another_column
> 1 Fname1 Lname1 asdfj
> 2 Fname2 Lname2 NULL
> 3 Fname3 Lname3 lsadfj
> 4 Fname4 Lname4 NULL
> 5 Fname5 Lname5 NULL

> i fname lname another_column
> 1 Fname1 Lname1 NULL
> 2 Fname2 Lname2 NULL
> 3 Fname3 Lname3 NULL
> 6 Fname6 Lname6 NULL
> 7 Fname7 Lname7 NULL

> You can use the sample script to create the tables as
> temporary tables.
> set nocount on
> create table #tmp1 (
> i int,
> fname char(20),
> lname char(20),
> another_column char(20) null
> )
> create table #tmp2 (
> i int,
> fname char(20),
> lname char(20),
> another_column char(20) null
> )

> insert into #tmp1
> select 1, 'Fname1', 'Lname1', 'asdfj'

> insert into #tmp1
> select 2, 'Fname2', 'Lname2', null

> insert into #tmp1
> select 3, 'Fname3', 'Lname3', 'lsadfj'

> insert into #tmp1
> select 4, 'Fname4', 'Lname4', null

> insert into #tmp1
> select 5, 'Fname5', 'Lname5', null

> insert into #tmp2
> select 1, 'Fname1', 'Lname1', null

> insert into #tmp2
> select 2, 'Fname2', 'Lname2', null

> insert into #tmp2
> select 3, 'Fname3', 'Lname3', null

> insert into #tmp2
> select 6, 'Fname6', 'Lname6', null

> insert into #tmp2
> select 7, 'Fname7', 'Lname7', null

> select * from #tmp1
> select * from #tmp2

> drop table #tmp1
> drop table #tmp2
> set nocount off

> Please try to help me.

> Thanks.

SELECT COALESCE(#tmp1.i, #tmp2.i),
       COALESCE(#tmp1.fname, #tmp2.fname),
       COALESCE(#tmp1.lname, #tmp2.lname),
       COALESCE(#tmp1.another_column, #tmp2.another_column)
  FROM #tmp1
  FULL OUTER JOIN #tmp2
    ON #tmp1.i = #tmp2.i
 WHERE 0 IN (COALESCE(#tmp1.i, 0), COALESCE(#tmp2.i, 0))

--
- Anith

set nocount on

create table t1 (
i int,
fname char(20),
lname char(20),
another_column char(20) null
)
create table t2 (
i int,
fname char(20),
lname char(20),
another_column char(20) null
)

insert into t1
select 1, 'Fname1', 'Lname1', 'asdfj'

insert into t1
select 2, 'Fname2', 'Lname2', null

insert into t1
select 3, 'Fname3', 'Lname3', 'lsadfj'

insert into t1
select 4, 'Fname4', 'Lname4', null

insert into t1
select 5, 'Fname5', 'Lname5', null

insert into t2
select 1, 'Fname1', 'Lname1', null

insert into t2
select 2, 'Fname2', 'Lname2', null

insert into t2
select 3, 'Fname3', 'Lname3', null

insert into t2
select 6, 'Fname6', 'Lname6', null

insert into t2
select 7, 'Fname7', 'Lname7', null

select * from t1
select * from t2

SELECT t1.i, t1.fname, t1.lname, t1.another_column
FROM t1
LEFT JOIN t2
ON t1.i = t2.i AND t1.fname = t2.fname AND t1.lname = t2.lname
WHERE t2.i IS NULL
UNION ALL
SELECT t2.i, t2.fname, t2.lname, t2.another_column
FROM t2
LEFT JOIN t1
ON t2.i = t1.i AND t2.fname = t1.fname AND t2.lname = t1.lname
WHERE t1.i IS NULL

drop table t1
drop table t2
set nocount off

Result:

i           fname                lname                another_column      
----------- -------------------- -------------------- --------------------
1           Fname1               Lname1               asdfj              
2           Fname2               Lname2               NULL
3           Fname3               Lname3               lsadfj              
4           Fname4               Lname4               NULL
5           Fname5               Lname5               NULL

i           fname                lname                another_column      
----------- -------------------- -------------------- --------------------
1           Fname1               Lname1               NULL
2           Fname2               Lname2               NULL
3           Fname3               Lname3               NULL
6           Fname6               Lname6               NULL
7           Fname7               Lname7               NULL

i           fname                lname                another_column      
----------- -------------------- -------------------- --------------------
4           Fname4               Lname4               NULL
5           Fname5               Lname5               NULL
6           Fname6               Lname6               NULL
7           Fname7               Lname7               NULL

AMB

SELECT * FROM
(SELECT * FROM #tmp1
UNION ALL
SELECT * FROM #tmp2) AS u
WHERE NOT EXISTS
(SELECT * FROM #tmp1 join1 JOIN #tmp2 join2
ON  join1.i=join2.i AND join1.fname=join2.fname AND join1.fname=join2.fname
AND join1.i=u.i     AND join1.fname=u.fname     AND join1.fname=u.fname)

"UNION ALL" might avoid a redundant step of eliminating /one/
duplicate copy from the UNION; it will explicitly prevent such
a step, but I'm not sure whether the query optimizer will notice
that it is a redundant step even without the word "ALL".

I /think/ this is different from everyone else's version, which
is what makes this game fun ;-)