Pls help. urgently required

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

subs

ozip dzip consignee shipdate
0101 3456 basf 2/1/2009
0104 3456 basf 2/1/2009
0890 2345 basf 3/4/2009
0105 3456 basf 2/1/2009




I have a table which has four fields- ozip, dzip, consignee,
shipdate. i want to extract all the rows with same dzip,
consignee, shipdate but with DIFFERENT ozip. In the above example,
the first, second and the fourth rows are extracted.

Can i pls get a SQL query which can extract only records that meet the
above described condition. The query should run fast and without any
problems. I have a query right now which corrupts and slows the entire
database. Thanks for any help . Multiple methods or solutions even
better.
 
SELECT X.*
FROM Sometable as X INNER JOIN SomeTable as Y
ON X.Dzip = Y.Dzip
AND X.Consignee = Y.Consignee
AND X.ShipDate = Y.ShipDate
AND X.Ozip <> Y.Ozip

Or

SELECT *
FROM SomeTable As Y
WHERE Exists
(SELECT Ozip
FROM SomeTable as X
WHERE X.Dzip = Y.Dzip
AND X.Consignee = Y.Consignee
AND X.ShipDate = Y.ShipDate
AND X.Ozip <> Y.Ozip)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
ozip dzip consignee shipdate
0101 3456 basf 2/1/2009
0104 3456 basf 2/1/2009
0890 2345 basf 3/4/2009
0105 3456 basf 2/1/2009




I have a table which has four fields- ozip, dzip, consignee,
shipdate. i want to extract all the rows with same dzip,
consignee, shipdate but with DIFFERENT ozip. In the above example,
the first, second and the fourth rows are extracted.

Can i pls get a SQL query which can extract only records that meet the
above described condition. The query should run fast and without any
problems. I have a query right now which corrupts and slows the entire
database. Thanks for any help . Multiple methods or solutions even
better.
Click to expand...

This will work better if you have a three-field Index in both tables on dzip,
consignee, and shipdate. You can then create a Totals query grouping by dzip,
consignee, and shipdate, and Counting ozip; select those records where the
count is greater than 1.

If you want to see the ozip values, join the totals query to the original
table by the three grouping fields.
 
Back
Top