[Sheist]

Hi guys,

I'm trying to solve a little sql problem -- I need to cross reference and dump out a list of records that don't belong. The existing query uses a left join between two tables... I need to add some more 'AND' statements in the WHERE line because the query needs to take into account other tables... hence I need to include those tables in the from line. How do I do this, when there is a left join already there?

For example:

SELECT DISTINCTROW tblOne.*
FROM tblOne LEFT JOIN tblTwo ON [tblOne].[item1]=[tblTwo].[item2]
WHERE ((((DatePart("m",[Date])=[Enter Month])) And [tblTwo].[item2] Is Null));

How can I go about adding:

WHERE ........ AND [tblThree].[item3] Is Null AND [tblFour].[item4] Is Null));

To the end of that?

Quick responses will be much, much appreciated.

Thanks guys!
-Sheist

 

[deuce868]

Try looking into how to do sub-queries for your server that you're using. What you do is perform a subquery and then query the results of that to narrow down further.

 

[Rockn]

You need to also have the other tables in the SELECT and the fields in the FROM portiont of the query with the appropriate joins as well.

 

[Sheist]

Ok, this is what I have come up with so far:

SELECT DISTINCTROW tblOne.*
FROM ((tblOne LEFT JOIN tblTwo ON [tblOne].[item1]=[tblTwo].[item2]) LEFT JOIN tblThree ON [tblOne].[item1]=[tblThree].[item3]) LEFT JOIN tblFour ON [tblOne].[item1]=[tblFour].[item4]
WHERE (((DatePart("m",[Date]))=[Enter Month]) And (([tblThree].[item3]) Is Null) And (([tblFour].[item4]) Is Null) And (([tblTwo].[item2]) Is Null));

It runs, but I'm not sure that I'm getting the correct result. Is there a logic error somewhere?

PS. Not really sure how to do subqueries - I'd appreciate a little explanation Or, I could try and learn about them on the weekend... if I get time :-/.

Thanks for the help so far.
-Sheist

 

[Sheist]

Hrm... can nobody see the logic error in my code? It's driving me nuts...

 

[Rockn]

If you are not sure the results are correct you will never get the query right. Can you put in some canned data you know the values will come out correct for and base your query on thoes?