Query with Join and where

spec

Registered User.
Local time
Today, 09:23
Joined
Jun 9, 2005
Messages
12
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
 
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
 
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
 
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.
 

Users who are viewing this thread

Back
Top Bottom