Tricky JOIN statement - please help :-(

Paul_Randall

Oh boy, where do I start.

I am trying to use a JOIN (of whatever type) to look for the records within a table (call it "biglist") that are not in another table (call it "smalllist").

So, if in biglist I have rec1, rec2, rec3, rec4, rec5 and in smalllist I have rec1, rec2, rec3. I want my sql to return rec4, rec5.

The fields are biglist.field1, biglist.field2, smalllist.field1, smalllist.field2

I tried the following:
SELECT DISTINCT biglist.field1, biglist.field2 FROM biglist LEFT JOIN smalllist ON (biglist.field1 != smalllist.field1)

Needless to say "IT DIDN'T WORK - aaarrrggghhh!!!"

I got a list of all of the distinct field1 and field2 from biglist

I have also tried LEFT OUTER JOIN but same result.

Thanks loads in advance. This is really bugging me and I need to finish it or I'll go insane.

Regards

Paul

Par

I think you need to use the Not Exists method i.e.
SELECT
FROM Table A
WHERE (NOT EXISTS
(SELECT
FROM Table B
WHERE Table B.Field = Table A.Field etc ))

HTH

Par

ultraslacker

What dbms?

Paul_Randall

Thanks Par but it didn't work either.

I failed to mention that I am using MySQL and I believe you cannot have a SELECT within another SELECT in MySQL. (I may be wrong but I'm sure I saw it on this board somewhere).

This is why I'm trying to do it with a JOIN method.

Sorry I didn't mention MySQL in my first post.

Paul_Randall

I already have MySQL installed on my machine as does my host. I prefer to use MySQL as I am more familiar with it and everything I do is built around it. No other reason.

Lars_Berg

SELECT biglist.field1, biglist.field2 
FROM biglist LEFT JOIN smalllist 
ON (biglist.field1 = smalllist.field1) 
where smalllist.field1 is null

Paul_Randall

That is it Lars. Thank you so very very much. It worked. So I was nearly there then I just needed the "where smallist.field1 = NULL" on the end.

Thanks also to everyone that has replied to this thread.

I'm so happy because I can finish this code off now.

This has been bugging me for ages.