Query minus Query

[1jet]

Hi all,

I have two queries, is it possible to minus one from another?

SELECT tblTrade.[Trade ID], tblTrade.[Trade] FROM tblTrade ;

MINUS

SELECT tblEmployeeTrade.[Trade ID], tblEmployeeTrade.[Employee ID] FROM tblEmployeeTrade WHERE tblEmployeeTrade.[Employee ID] = Forms!frmEditEmployee!cboSelectEmployee ;

 

[namliam]

Use an outer join

Query1
SELECT tblTrade.[Trade ID], tblTrade.[Trade] FROM tblTrade

Query2
SELECT tblEmployeeTrade.[Trade ID], tblEmployeeTrade.[Employee ID] FROM tblEmployeeTrade WHERE tblEmployeeTrade.[Employee ID] = Forms!frmEditEmployee!cboSelectEmployee ;

Query3
Select query1.*
from Query1
left join Query2 on [Query1].[Trade ID] = [Query2].[Trade ID]
where [query2].[Trade ID] is null

 

[1jet]

Cheers mailman....i was looking this though

SELECT tblTrade.[Trade ID], tblTrade.[Trade]
FROM tblTrade
WHERE tblTrade.[Trade ID]
NOT IN ( SELECT tblEmployeeTrade.[Trade ID]
FROM tblEmployeeTrade
WHERE tblEmployeeTrade.[Employee ID] = Forms!frmEditEmployee!cboSelectEmployee ) ORDER BY tblTrade.[Trade];

 

[namliam]

Your "not in" solution works, but is (much) slower than a Join solution.

 

[1jet]

your probably going to shake ur head at me...but right now im staying away from all joins...including left and right ones

i havent come across a good textbook or thread explaining to me what a left or right join is!

ive tried to understand it....but i always get confused as to what table gets joined to which side....
lol

 

[namliam]

I tried to explain left and right joins here:
http://www.access-programmers.co.uk/forums/showthread.php?t=160237&highlight=Join

Have a look there and see if you can get it... if you have any questions on that ask the question in that thread.

Joins are the CORE of databases, if you are not doing joins at all then you are going to run into serious problems fast! The time to start understanding and start using them is today *urm* yesterday, no better make that last week
Or when ever you start using databases!!!! (in my case some 15 years ago... *ouch* I am getting old LOL )

 

[1jet]

ahhh cheers for that......ill have a good read