Do not understand the result of a query from 4 tables

[Guest]

Hi
I have a table name FAACH. This table shows me I have 77 chemicals are active that mean 77 records. Then I make a query (condition is chemical must be active: means being used and in good condition) that will show me who are incharge for these chemicals, who supply and in what lab room...? That means I have to join 3 more tables to get these information. The result just shows me 45 records (It should be 77 records). But when I change the join properties to the third one (Include all records from .... table and only those records from .... table where the join fields are equal) the result is 77 records. I do not understand it. Are there any one who know about this, please explain
Thank you
Le Tran

 

[Rick Brandt]

Hi,
I have a table name FAACH. This table shows me I have 77 chemicals are

active that mean 77 records. Then I make a query (condition is chemical
must be active: means being used and in good condition) that will show me
who are incharge for these chemicals, who supply and in what lab room...?
That means I have to join 3 more tables to get these information. The
result just shows me 45 records (It should be 77 records). But when I
change the join properties to the third one (Include all records from ....
table and only those records from .... table where the join fields are
equal) the result is 77 records. I do not understand it. Are there any one
who know about this, please explain.

Thank you,
Le Tran

The standard join only returns records where matches are found in both
tables used in the join. If some of your chemicals have no matching
records in the other three tables then they are filtered out with a
standard join.

Example:

If I have a table CUSTOMERS with 1000 entries and a table ORDERS with only
ten entries (I just opened for business recently), then a query using both
of these tables with a standard join will only return those customers who
have actually placed orders. If I want to see ALL customers, then I have
to change to an outer join.

 

[Guest]

Hi Rick

How about supplier name for all of these orders? What happen if all 10 orders are in one supplier. And when join all of these tables supply, order and customer. How many records will show if use the standard join? One record will show or 10 records becase it has one supplier for all of these orders. I think that if use the third join properties it will show what we want. Could you please explain a little bit more?
Thank you
Le Tran

 

[Rick Brandt]

Hi Rick,

How about supplier name for all of these orders? What happen if all 10

orders are in one supplier. And when join all of these tables supply, order
and customer. How many records will show if use the standard join? One
record will show or 10 records becase it has one supplier for all of these
orders. I think that if use the third join properties it will show what we
want. Could you please explain a little bit more?

Thank you,
Le Tran

You would get ten rows providing that every one of the ten orders had a
matching customer from the customers table. It does not matter if that
consists of ten different customers or if one customer placed all ten of
the orders.

 

[Guest]

Hi

When do we need to use the third join properties
Le Tran