Shariman:

You are, by default, using what's known as an INNER JOIN with the syntax 
you've provided.  An inner join will return a result set containing data 
from records that exist in BOTH tables, and will exclude data from records 
that don't have a corresponding match.

What you need in this situation is either a LEFT JOIN or a RIGHT JOIN.  
The LEFT JOIN allows you to specify that the query should include ALL 
records on the left side of the LEFT JOIN clause, and all matching records 
on the right side of the clause.

The RIGHT JOIN clause is basically synonymous, except that the query will 
return ALL rows from the table on the right side of the RIGHT JOIN clause, 
and only matching records from the table on the left side of the clause.

How about a quick example...

tblPeople
=========
  ID   vchLastName  vchFirstName
  ===  ===========  ================
  1    Goofy        I.M.
  2    Duck         Donald
  3    Mouse        Mickey

tblPhoneNumbers
===============
  PersonID  vchPhoneNumber
  ========  ==============
  1         555-1212
  3         123-4567

The next examples should illustrate the difference.  First, let's show a 
query containing the default INNER JOIN.

  SELECT
    p.ID,
    p.vchFirstName,
    p.vchLastName,
    pn.vchPhoneNumber
  FROM tblPeople p
  INNER JOIN tblPhoneNumbers pn
    ON p.ID = pn.PersonID
  ORDER BY
    p.vchLastName,
    p.vchFirstName

Returns the following (note that record #2 is omitted):

  ID          vchFirstName vchLastName vchPhoneNumber 
  ----------- ------------ ----------- -------------- 
  1           Goofy        I.M.        555-1212
  3           Mouse        Mickey      123-4567

Trying a LEFT JOIN, and ensuring that the table from which we want ALL 
records returned is on the left side of the LEFT JOIN clause, we will 
return all records in the "left" table, and all matching records in 
the "right" table:

  SELECT
    p.ID,
    p.vchFirstName,
    p.vchLastName,
    pn.vchPhoneNumber
  FROM tblPeople p                 -- Include ALL records from this table
  LEFT JOIN tblPhoneNumbers pn
    ON p.ID = pn.PersonID
  ORDER BY
    p.vchLastName,
    p.vchFirstName

Returns:

  ID          vchFirstName vchLastName vchPhoneNumber 
  ----------- ------------ ----------- -------------- 
  1           Goofy        I.M.        555-1212
  2           Duck         Donald      NULL
  3           Mouse        Mickey      123-4567

Note that the sole record not containing a match in the phone numbers 
table simply inserted a NULL in the vchPhoneNumber field for record #2.

Finally, you could construct a query yielding similar results using a 
right join, but you would have to ensure that you put the table that 
should return ALL records on the right side of the RIGHT JOIN clause.

  SELECT
    p.ID,
    p.vchFirstName,
    p.vchLastName,
    pn.vchPhoneNumber
  FROM tblPhoneNumbers pn
  RIGHT JOIN tblPeople p      -- Include ALL records from this table
    ON p.ID = pn.PersonID
  ORDER BY
    p.vchLastName,
    p.vchFirstName

Returns the same results as the previous query:

  ID          vchFirstName vchLastName vchPhoneNumber 
  ----------- ------------ ----------- -------------- 
  1           Goofy        I.M.        555-1212
  2           Duck         Donald      NULL
  3           Mouse        Mickey      123-4567

Note that LEFT and RIGHT joins become a bit trickier when you start 
building more complex queries.  Specifically, when you are including more 
than 2 tables.  You may want to refer to a Wrox book (how's that for a 
plug) in these situations.  For a hint, you basically want to ensure that 
you don't put an INNER join _after_ the LEFT or RIGHT join in your SQL 
statement.  Doing so will negate the effect of selecting all records from 
the table(s).  Cheers.

- Roger Nedel

  Nedel Software Solutions
  rnedel@b...

==========================

> I have the Select statement like below, where it's a combination of 
three 
> tables(Company, Category, Topics) 
> 
> sql = "SELECT DISTINCT Company.ID As ID," 
> sql = sql & " Company.Name As Name, Company.Business, 
Company.FlagTopics" 
> sql = sql & " FROM Company, Category, Topics" 
> sql = sql & " WHERE Company.CatID = Category.ID" 
> sql = sql & " AND Company.ID = Topics.CompID" 
> sql = sql & " AND (Company.[Name] LIKE '%" & Name & "%'" 
> sql = sql & " OR Company.Business LIKE '%" & Name & "%'" 
> sql = sql & " OR Category.[Name] LIKE '%" & Name & "%'" 
> sql = sql & " OR Topics.[Name] LIKE '%" & Name & "%'" 
> sql = sql & " OR Topics.Description LIKE '%" & Name & "%')" 
> sql = sql & " ORDER BY Company.Name" 
> sql = sql & ";" 
> 
> My problem is, If the record in table Company doesn't have any 
associated 
> record in the table Topics(where Company.ID(PK) not exist in 
Topics.CompID
> (FK)), the record for that company will not be selected. 
> 
> How can I change that select statement to make the record in the table 
> Company become selected either it is associated record in table Topics 
or 
> not. 
>