Re: Query to extract one record from multiple records
From: <cbursell_at_geusnet.com>
Date: Tue, 11 Oct 2011 07:46:31 -0700 (PDT)
Message-ID: <39fd83de-83e3-4364-92c6-d12f79842dd5_at_k34g2000yqm.googlegroups.com>
I did a "quick and dirty" test with Joel's answer using a local copy of sqlite
I had to reverse the GROUP BY and Having so it came out like:
Date: Tue, 11 Oct 2011 07:46:31 -0700 (PDT)
Message-ID: <39fd83de-83e3-4364-92c6-d12f79842dd5_at_k34g2000yqm.googlegroups.com>
I did a "quick and dirty" test with Joel's answer using a local copy of sqlite
I had to reverse the GROUP BY and Having so it came out like:
SELECT ITEM, DESCRIPTION, LOCATION, BIN
FROM MTABLE
WHERE ITEM IN (SELECT ITEM
FROM MTABLE GROUP by ITEM, LOCATION HAVING COUNT(*) > 1)
AND LOCATION='WAREHOUSE'
UNION ALL
SELECT ITEM, DESCRIPTION, LOCATION, BIN
FROM MTABLE
WHERE ITEM IN (SELECT ITEM
FROM MTABLE GROUP by ITEM, LOCATION HAVING COUNT(*) = 1)
The above returns both results for ITEM 1234 (Only wanted the one with
WAREHOUSE)
Back to the drawing board
1234 RED LAMP WAREHOUSE C132 1234 RED LAMP STOREROOM FLOOR 3456 TABLE STOREROOM X123 4231 CHAIR WAREHOUSE A432Received on Tue Oct 11 2011 - 09:46:31 CDT