Problems with a Left Join

[modalman]

Hi. I am using ASP to query an access database. So far I have used left join to join table2 to table1 where table2.fld1=table1.fld1. That works fine. The SQL is:

SELECT * FROM tbl1 LEFT JOIN tbl2 ON tbl1.fld1=tbl2.fld1

The problem I have is that I only want to use certain records from tbl2. So tbl2 needs to be left joined to tbl1 where fld1 is identical but also where tbl2.fld2 equals a certain value. Any help will be aprreciated.
Many thanks in advance. Modalman

ASCII silly question, get a silly ANSI

 

[BNPMike]

Just add WHERE TBL2.FLD2 = 'valreqd';

 

[modalman]

Thanks Mike. I did try that and it filtered all the records in the recordset. The where clause seemed to execute after the join has been made removing records from tbl1 as well as tbl2. I did place the where clause at the end of the sql statement above. Is it possible that it should be placed elsewhere in the statement?


ASCII silly question, get a silly ANSI

 

[cmmrfrds]

Table 2 field 1 is NULL when they are not equal.


SELECT * FROM tbl1 LEFT JOIN tbl2 ON tbl1.fld1=tbl2.fld1
and (tbl2.fld1 IS NULL and tbl1.fld1 = "something&quot

 

[cmmrfrds]

Meant OR.

SELECT * FROM tbl1 LEFT JOIN tbl2 ON tbl1.fld1=tbl2.fld1
OR (tbl2.fld1 IS NULL and tbl1.fld1 = "something&quot

 

[TonyJollans]

Hi modalman,

You are right about the WHERE - it filters after the JOIN, but you should be able to restrict the records brought into the join like this ..

SELECT * FROM tbl1 LEFT JOIN tbl2 ON (tbl1.fld1 = tbl2.fld1 AND tbl2.fld2 = "yourvalue"

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.