RE: accidentally creating duplicate data when querying 2 tables
- From: Ken Sheridan <KenSheridan@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 7 Aug 2008 16:54:01 -0700
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 hacker" wrote:
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).
"akphidelt" wrote:
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.
"access hacker" wrote:
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?
.
- References:
- accidentally creating duplicate data when querying 2 tables
- From: access hacker
- RE: accidentally creating duplicate data when querying 2 tables
- From: akphidelt
- RE: accidentally creating duplicate data when querying 2 tables
- From: access hacker
- accidentally creating duplicate data when querying 2 tables
- Prev by Date: Re: Can a Add a String to a Union Query?
- Next by Date: Re: Can a Add a String to a Union Query?
- Previous by thread: Re: accidentally creating duplicate data when querying 2 tables
- Next by thread: RE: accidentally creating duplicate data when querying 2 tables
- Index(es):
Relevant Pages
|
