accidentally creating duplicate data when querying 2 tables

[access hacker]

I need to pull currency data from two tables yet when i create the query to
bring them together it creates a "cartesian product" (duplicates information
when there is one record in the parent table and 0 to many records in the
line item table). I read in a SQL for Dummies that you can create a "Union"
query and then do a "Coalesce" statement. Can this be done? Or is there
some way to get "Null" values for the records which don't have a record in
the other table?

 

[akphidelt]

What kind of join do you have going on and how are the tables set up. If you
are getting the duplicate entries then you have a bad grouping set up or
something with how the query is set up. But typically you won't need a union
query to do most joins.

 

[Lord Kelvan]

if you want null values you have to use a left join read up about that
and it may help you

 

[access hacker]

I simply have an inner join query and was thinking I needed to create a
different form of query. I have a primary table and a line item table that
is associated to it with the primary table's primary key. I am trying to
filter on a parameter value that will be in either the primary table or the
line item table. So I need it to say "show the record where "x" is found in
"COA" field or where "x" is found in Line Item Tb "LICOA" field. Yet, if "x"
is found two or more times in the Line item table, it tries to duplicate info
from the first table on the second row (record).

 

[Lord Kelvan]

ok in your select statement make it select distinct and if you want to
select values from table1 where there are no values in table 2 as well
as if there are records that have values in table2 use a left join not
a inner join

 

[Ken Sheridan]

The query will only return the Cartesian product of the two tables if they
are not explicitly joined, either in a JOIN clause or by a join criterion in
the WHERE clause. The Cartesian product is when every row in one table is
joined to every row in the other, so if there are 100 rows in one, and 1,000
rows in the other the result will be 100,000 rows.

If the tables are INNER JOINed, then for each row in the referenced (parent)
table the number of rows returned will be the number of matching rows in the
referencing (child) table. An INNER JOIN will not return any rows from the
referenced table which do not have at least one matching row in the
referencing table, but you can do so by using an OUTER JOIN (which come in
LEFT and RIGHT flavours), so if your tables are Orders and LineItems say, a
query which returns all orders, along with data from the matching rows from
the LineItems table, and NULLs in the LineItems columns where there is no
match might be something like this:

SELECT CustomerID, OrderDate, ItemID, UnitPrice, Quantity
FROM Orders LEFT JOIN LineItems
ON Orders.OrderID = LineItems.OrderID;

If you wanted zeros returned in place of the NULLs in the case of Orders
with no matching Lineitem records you can use the NZ function:

SELECT CustomerID, OrderDate, NZ(ItemID,0) AS item_ID,
NZ(UnitPrice,0) AS Unit_Price, NZ(Quantity,0) As Quantity_Ordered
FROM Orders LEFT JOIN LineItems
ON Orders.OrderID = LineItems.OrderID;

The SQL COALESCE function works rather differently from the VBA NZ function.
The former scans a value list and returns the first NOT NULL value. As far
as I know its not supported by Jet SQL. The NZ function returns the value of
the first argument if NOT NULL, the value of the second argument if the first
is NULL. THE COALESCE function can be used in this way of course in standard
SQL, where COALESCE(ItemID, 0) would be the equivalent of NZ(ItemID,0) above.

One thing to note about OUTER JOINs is that you can not restrict the result
set on the basis of values in the table on the outer side of the join, so you
could not have:

SELECT CustomerID, OrderDate, ItemID, UnitPrice, Quantity
FROM Orders LEFT JOIN LineItems
ON Orders.OrderID = LineItems.OrderID
WHERE UnitPrice > 100;

But you could have:

SELECT CustomerID, OrderDate, ItemID, UnitPrice, Quantity
FROM Orders LEFT JOIN LineItems
ON Orders.OrderID = LineItems.OrderID
WHERE YEAR(OrderDate) = 2008;

The former would in fact return exactly the same results as an INNER JOIN.

In a case like the above, where multiple rows per order are, quite
correctly, returned by the query this would normally be where the query is
being used as the basis for a report. The report can then be grouped by
OrderID and the order data put in a group header with the line items in the
detail section, thus avoiding any duplication in the final presentation of
the data. Ina form the more common approach would be to have a form based on
Orders and a subform based on LineItems rather than using a query which joins
the tables as the basis for a single form.

Ken Sheridan
Stafford, England

 

[Ken Sheridan]

As I've said in my other response you could only apply a parameter to a
column on the LEFT side of a LEFT OUTER JOIN, so while you'd be able to do so
in the case of a column in the 'primary' table you cannot do so in the case
of a column in the LineItems table. You can only do that with an INNER JOIN,
so if you want to apply parameters to both, and at the same time return rows
form the 'primary table where there are no matching LineItems rows then you'd
need to use a subquery, e.g.

SELECT CustomerID, OrderDate, NZ(ItemID,0) AS item_ID,
NZ(UnitPrice,0) AS Unit_Price, NZ(Quantity,0) As Quantity_Ordered
FROM Orders AS O1 LEFT JOIN LineItems AS LI1
ON O1.OrderID = LI1.OrderID
WHERE OrderDate >= [Enter date from:]
OR EXISTS
(SELECT *
FROM LineItems AS LI2
WHERE LI2.OrderID = O1.OrderID
AND Quantity > [Enter minimum quantity:]);

This would handle the rather unrealistic scenario where you want to return
all orders after a certain date, or orders of any date where the quantity of
any item ordered is greater than a specific quantity. Unrealistic as this
may be it does illustrate the principles involved.

Ken Sheridan
Stafford, England

 

[access hacker1]

To Ken Sheridan
I truly appreciate your help. You were very informative and I was able to
solve my problem as well as understand more about joins and cartesian
products. I would very much like to open a line of communication with you.
You can email me at [email protected] Once again, thank you.