Select records from one table not in another 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

Transact-SQL (2000)

Select records from one table not in another table

Author

Topic

badname
Starting Member

5 Posts

Posted - 2004-12-07 : 07:56:44

I spent all afternoon trying to solve this but I can't.

I have 2 tables (called Table1 and Table2). Each table has 6 fileds (f1, f2, f3, f4, f5, f6). Table1 and Table2 have 3 fields (f1,f2, f3) that present the same data.
Now I want to select records in Table1
where f1 of Table1 is not the same as f1 of Table2
AND f2 of Table1 is not the same as f2 f Table2
AND f3 of Table1 is not the same as f3 of Table2

I tried all LEFT/RIGHT/INNER join, ect. don't know what to do now.
Please help.
thank you very much
Tim

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-07 : 08:02:00

I'm prety sure you don't want what you are saying.
Now I want to select records in Table1
where f1 of Table1 is not the same as f1 of Table2

do you mean there isn't an entry in t2 for that f1 in t1
or
there is an entry in t2 for a different f1 (i.e. all of t1 if there are two values in t2)
or
the entry in t2 with the same pk has a different value for f1

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

wtech
Starting Member

11 Posts

Posted - 2004-12-07 : 08:16:57

hi,
i hope i have understood u correctly.just try this out

select table1.f1,table1.f2,table1.f3,table1.f4,table1.f5,table1.f6 from table1,table2 where table1.f1=table2.f1

wtech
Starting Member

11 Posts

Posted - 2004-12-07 : 08:37:50

hi,
this one will give u the differences in the two tables and the one i have posted above will give u the similarities.try them out.
select distinct(table1.f1) from table1 where table1.f1<> all (select table2.f1 from table2)

badname
Starting Member

5 Posts

Posted - 2004-12-07 : 21:12:51

Thanks guys and sorry for the mess. I didn't try your solution yet and I will after writing this.

I don't like what I'm facing as well. Actaully this is a bad database design. It should't happen but it did.
I am a fresh gradutate string working for 4 days and I get my firts assignment.

Here what happened. A company selling some kind of toys. It maintain customers information 2 tables, one for year 2000 and one for year 2001. The two have identical fields.
Some customers who buy a toy in year 2000 (company put record in year 2000) may buy it again in 2001 (company record in year 2001, again) The company want to know who is the customer in year 2001 but not in 2000.

The criteria is that f1, f2, f3 distinguish each customer. If all f1, f2 and f3 of table year 2000 match f1, f2 and f3 in table year 2001, the company concludes that this customer is the same customer. If f1, f2 is the same but f3 not the same, this is not the same customer.

The requirement is to get customers in year 2001 but not in year 2000.

thanks very much

wtech
Starting Member

11 Posts

Posted - 2004-12-07 : 23:35:02

ok just try the solutions mentioned above and let me know if they have solved ur problem

badname
Starting Member

5 Posts

Posted - 2004-12-08 : 01:41:27

both solutions generate results but the results don't fully satisfy the requirement. We set criteria that f1 of table is not in f1 in table2.

Case1
suppose here T1
Max 123WE 8009 jjy ool
Jack 123UU 2990 UIIp opp
Jane 123WQ 900 OOU qw

here T2
Max 123WE 8009 jjy ool
Jack 123UU 2990 UIIp opp
Jane 123WQ 900 OOU qw

This case all coresponding fields (f1, f2, f3) are match.
Result : no row
This is ok since all fields are the same.

Case2
I change T2 from Jane to Jan
Max 123WE 8009 jjy ool
Jack 123UU 2990 UIIp opp
Jan 123WQ 900 OOU qw

result:
Jane 123WQ 900 OOU qw

This is ok since Jan is not Jane and we don't care what f2 amd f3 are..

Case 3
I change in T2
f2 from 123WQ to 123WR
Max 123WE 8009 jjy ool
Jack 123UU 2990 UIIp opp
Jane 123WR 900 OOU qw

Result: no row
This is not ok. Jane and Jane may be the same but f2 in T1 and f2 in T2 of that record does not match. We know that these 2 records are different. SQL only set criteria to see if Jane exist in T2 or not. i think there should be 2 more criteria...!? can't figure out a better way of thinking of this..

badname
Starting Member

5 Posts

Posted - 2004-12-08 : 02:23:18

wtech,
I think I just got a clue
i used you solution and modified like this

select table1.* from table1
where table1.f1 <> all (select table2.f1 from table2)
union
(select table1.* from table1
where table1.f2<> all (select table2.f2 from table2))
union
(select table1.* from table1
where table1.f3 <> all (select table2.f3 from table2))

!??
it generate result but still not sure it is right
I have to test with real tables.
is the logic behind this right?

badname
Starting Member

5 Posts

Posted - 2004-12-08 : 03:38:54

i tested with the real tables with 20,000+ records
still not 100% correct...!?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-12-08 : 05:23:05

the following link may also be of help.....

http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-08 : 10:00:12

Will either of these work?,
or am I missing the point here...

select
	t1.*
from
	table1 t1
	left join table2 t2
on
	t1.f1 = t2.f1
	and t1.f2 = t2.f2
	and t1.f3 = t2.f3
where
	t2.f1 is null

select
	t1.*
from
	table1 t1
where
	not exists(
		select * from table2 t2
		where t1.f1 = t2.f1
		and t1.f2 = t2.f2
		and t1.f3 = t2.f3 )

rockmoose

wtech
Starting Member

11 Posts

Posted - 2004-12-10 : 00:34:02

hi,
just try this query. i have tried them with the scenario u have specified.it is working chcek this out with ur original table
select distinct(table1.f1) from table1 where table1.f1<> all (select table2.f1 from table2) or table1.f2 <> all(select table2.f2 from table2) or table1.f3 <> all(select table2.f3 from table2)

mprolli
Starting Member

24 Posts

Posted - 2004-12-11 : 18:52:31

Couldn't you just concat the f1, f2, and f3 fields in table1 and compare them to the cocat'd f1, f2, f3 fields from table2

Select [id]
from table1
where (cast(F1 as varchar)+Cast(f2 as varchar)+cast(f3 as varchar)
not in (select cast(F1 as varchar)+Cast(f2 as varchar)+cast(f3 as varchar) from table2))
Union
Select [id]
from table2
where (cast(F1 as varchar)+Cast(f2 as varchar)+cast(f3 as varchar)
not in (select cast(F1 as varchar)+Cast(f2 as varchar)+cast(f3 as varchar) from table1))

Let us rise up and be thankful, for if we didn't learn a lot today, at least we learned a little, and if we didn't learn a little, at least we didn't get sick, and if we got sick, at least we didn't die; so, let us all be thankful.
--Buddha

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources