Query with Join and where

[spec]

I need to write a query which retrieves all the records from first table and those matching from second table and also satisfies a criteria.

I had

Table 1
F1
F2
F3

Table 2
F4
F5
F6
F7

I had to get all the records from Table1 and also those matching with Table2
So i did left join and getting all the records.
And I also need to check whether F3 in Table1 is in between F6 and F7 of Table2.

So I wrote query like this

SELECT Table1.F1, Table1.F2, Table2.F5
FROM Table1 LEFT JOIN Table2 ON (Table1.F1 = Table2.F4) WHERE Table1.F3 BETWEEN Table2.F6 AND Table2.F7;

I am getting only those records which are matching where condition.
I need to get all the records including which match the criteria and also those which don't match.

Please any suggestions?

Thanks

 

[WayneRyan]

Spec,

Isn't that the purpose of the Where clause.

You initially have all records from Table1.

Adding the Left Join to Table2. If Table1 has a counterpart in Table2, you
add Table2's fields (Null fields if no counterpart.

You added a Where clause to restrict the number of Table1 <--> Table2
records to only the ones where Table1.F3 was between Table2's F6 & F7.

You've joined the tables, and selected the desired rows. You can get
the "other" rows by the converse (F3 not between F6 & F7). You could
always do a UNION query and get back to where you started.

Wayne

 

[spec]

Thanks Wayne.

I am trying to do so.
But not getting all the records.
I think i am doing some thing wrong.
Could u please give me the query.

Thanks,
Spec

 

[spec]

I got where the problem is ,
Null values are present for F6 and F7
so i am not getting the records which are not between F6 and F7.