SQL query ;)

1663 Views 5 Replies 3 Participants Last post by Rockn, Jan 18, 2005

Sheist

Discussion Starter · Jan 12, 2005

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

Save

Status: Not open for further replies.

1 - 3 of 6 Posts

Sheist

Discussion Starter · #4 · Jan 13, 2005

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

Save

Sheist

Discussion Starter · #5 · Jan 18, 2005

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

Save

1 - 3 of 6 Posts

Status: Not open for further replies.

Top Contributors this Month