have productids that don't have sales & saleof products not on pro

[babs]

I am trying to run a complet query and see ALL records
I have product table and a sales table that I am joining. I have use the
outer join getting all products in list even if no sales - but that excludes
some of the sales or Products on the sales table that are NOT on the Prod
table. This data is coming from a client and I do not have Control to make
them match.

Just really want ALL of the records to show UP - all products even if no
sales and with sales and all products even though they may not be on the
product list.


any easy way to do this - two outer joins and then rejoin somehow??? what
am I missing
Thanks,
Barb

 

[Ryan]

I have this same problem, I get Hospital and patient information. I show all
hospitals and only patients for that hosptial. Sometimes there is not a
hospital listed in the Patient table so that patient doesnt show up. I
solved this problem by finding those patients that dont have a hospital in
another query, and then adding a hospital. If you try to join non related
data you will get a huge number of results because every non related value
will be compared to every related value and produce another result. Find
your non related values and give them a value is my suggestion.

 

[babs]

want a way for access to do it by running combo of queries or some other way

 

[Marshall Barton]

I am trying to run a complet query and see ALL records
I have product table and a sales table that I am joining. I have use the
outer join getting all products in list even if no sales - but that excludes
some of the sales or Products on the sales table that are NOT on the Prod
table. This data is coming from a client and I do not have Control to make
them match.

Just really want ALL of the records to show UP - all products even if no
sales and with sales and all products even though they may not be on the
product list.


any easy way to do this - two outer joins and then rejoin somehow??? what
am I missing

Union the two queries. E.g.

SELECT products.*, sales.*
FROM products LEFT JOIN sales
ON products.id = sales.productid
UNION ALL
SELECT products.*, sales.*
FROM products RIGHT JOIN sales
ON products.id = sales.productid
WHERE products.id Is Null