Pls help. urgently required

[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.

 

[John Spencer]

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
'====================================================

 

[MGFoster]

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.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Possibly this:

SELECT T1.ozip, T1.dzip, T1.consignee, T1.shipdate
FROM (the_table As T1
INNER JOIN the_table As T2
ON T1.dzip = T2. dzip
AND T1.consignee = T2.consignee
AND T1.shipdate = T2.shipdate)
INNER JOIN the_table As T3
ON T1.dzip = T3.dzip
AND T1.consignee = T3.consignee
AND T1.shipdate = T3.shipdate
WHERE T1.ozip <> T2.ozip
AND T1.ozip <> T3.ozip

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSpSQV4echKqOuFEgEQLK3QCfSZsb+v/4SQRKDz5JhTsvuxficukAn2Vo
snWWFAi2kPZhJXZbQGFGcF9S
=skSd
-----END PGP SIGNATURE-----

 

[John W. Vinson]

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.

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.