Pls correct this query

  • Thread starter Thread starter subs
  • Start date Start date
S

subs

SELECT X.*
FROM PPG AS X INNER JOIN PPG AS Y ON (X.BL<>Y.BL) AND
(X.Ship_Date=Y.Ship_Date) AND (X.Consignee=Y.Consignee) AND
(X.Ozip=Y.Ozip) AND (X.Dzip=Y.Dzip);

I have this query which returns all records with same shipdate, same
consignee, same ozip , same dzip but Different BL.

The above query is returnign records which satisfy the above condition
but giving me DUPLICATE RECORDS. For example a record which has to be
there only once is coming up twice. Can anyone pls correct the query.
Is it because of type of Joins.


Thanks for the help
 
SELECT DISTINCT X.*
FROM PPG AS X INNER JOIN PPG AS Y ON (X.BL<>Y.BL) AND
(X.Ship_Date=Y.Ship_Date) AND (X.Consignee=Y.Consignee) AND
(X.Ozip=Y.Ozip) AND (X.Dzip=Y.Dzip);

Note that the above query will eliminate duplicates, but will not be
updatable.
 
SELECT DISTINCT X.*
FROM PPG AS X INNER JOIN PPG AS Y ON (X.BL<>Y.BL) AND
(X.Ship_Date=Y.Ship_Date) AND (X.Consignee=Y.Consignee) AND
(X.Ozip=Y.Ozip) AND (X.Dzip=Y.Dzip);

Note that the above query will eliminate duplicates, but will not be
updatable.
--

        Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/









- Show quoted text -
Click to expand...

Thanks But why was the query initially giving me duplicates?
 
Without knowing your table structures and data, it's hard to give a
definitive answer. But usually one gets duplicates because you are not
joining on enough fields so that you get unique matching, or you have
duplicate data in one table.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


SELECT DISTINCT X.*
FROM PPG AS X INNER JOIN PPG AS Y ON (X.BL<>Y.BL) AND
(X.Ship_Date=Y.Ship_Date) AND (X.Consignee=Y.Consignee) AND
(X.Ozip=Y.Ozip) AND (X.Dzip=Y.Dzip);

Note that the above query will eliminate duplicates, but will not be
updatable.
--

Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/









- Show quoted text -
Click to expand...

Thanks But why was the query initially giving me duplicates?
 
Thanks But why was the query initially giving me duplicates?
Click to expand...

Because it's joining the records on Ship_Date, Consignee, OZip and DZip...

and if there are multiple values of BL, it's giving you *every possible
combination* of those values.

Let's say you have three records which match on the four fields, but have BL
values 1, 2 and 3. You'll get 1 paired with 2, 1 paired with 3, 2 paired with
1 (again, it's unequal!), 2 paired with 3, 3 paired with 1 (again), and 3
paired with 2 (again).

If you have more records, you'll get more pairings.
 
Back
Top