Re: Query to extract one record from multiple records

From: joel garry <joel-garry_at_home.com>
Date: Mon, 10 Oct 2011 16:29:33 -0700 (PDT)
Message-ID: <90e4cbd1-2fd4-4d87-a8b4-61e9dc7a9070_at_p29g2000pra.googlegroups.com>

On Oct 10, 2:14 pm, cburs..._at_geusnet.com wrote:
> Suppose I have a table like:
>
> ITEM   DESCRIPTION   LOCATION         BIN
> 1234    RED LAMP       WAREHOUSE    CL32
> 1234    RED LAMP       STOREROOM    FLOOR
> 3456    TABLE               WAREHOUSE    XL23
> 4231   CHAIR                 STOREROOM    M233
>
> I have a query like:
> SELECT ITEM, DESCRIPTION, LOCATION, BIN
> FROM <TABLE>
>
> However if there are more than one location for the ITEM I want the
> WAREHOUSE location
>
> For example I only want to return:
>
> 1234    RED LAMP       WAREHOUSE    CL32
> 3456    TABLE               WAREHOUSE    XL23
> 4231   CHAIR                 STOREROOM    M233
>
> Any ideas?

My brain isn't functioning on all cylinders, being that it is Monday afternoon, but I think it would be something like:

SELECT ITEM, DESCRIPTION, LOCATION, BIN
 FROM <TABLE>
WHERE ITEM IN (SELECT ITEM
 FROM <TABLE> HAVING COUNT(*) > 1 GROUP by ITEM, LOCATION) AND LOCATION='WAREHOUSE'
UNION ALL
SELECT ITEM, DESCRIPTION, LOCATION, BIN
 FROM <TABLE>
WHERE ITEM IN (SELECT ITEM
 FROM <TABLE> HAVING COUNT(*) = 1 GROUP by ITEM, LOCATION) ;

You might get better answers if you provide table creation and data loading commands.

jg

--
_at_home.com is bogus.
http://www.10news.com/news/29435424/detail.html

Received on Mon Oct 10 2011 - 18:29:33 CDT