Re: Query to extract one record from multiple records

From: <cbursell_at_geusnet.com>
Date: Tue, 11 Oct 2011 07:23:21 -0700 (PDT)
Message-ID: <fc9b29e5-c9b6-4023-a5d3-abc4405873cb_at_c1g2000yql.googlegroups.com>



On Oct 11, 1:03 am, Robert Klemme <shortcut..._at_googlemail.com> wrote:
> On 10.10.2011 23:14, 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
>
> What if there is more than one location but no WAREHOUSE location?
>
> > For example I only want to return:
>
> > 1234    RED LAMP       WAREHOUSE    CL32
> > 3456    TABLE               WAREHOUSE    XL23
> > 4231   CHAIR                 STOREROOM    M233
>
> > Any ideas?
>
> See Joel's reply.
>
> Kind regards
>
>         robert
>
> --
> remember.guy do |as, often| as.you_can - without endhttp://blog.rubybestpractices.com/

There will always be at least one location which will be either Warehouse or Storeroom. However, there are occasions where the same item is located in both locations. IN that event we wish to return the record with the Warehouse location and ignore the one with the Storeroom location. In the case of only one location, return that record.

I realize this is a very simplified example for a more complex query. I do not understand how the table creation and data loading commands will help. Am I missing something?

Here is perhaps a better example. We nned to get one record for each ITEM. If there are ITEMS in both locations we wish to select only the one with WAREHOUSE location

I hope this makes more sense. It has my head spinning :-)

SELECT DISTINCT IM.ITEM , IM.DESCRIPTION, IM.MANUF_NBR, IL.LOCATION FROM ITEMMAST IM
    RIGHT JOIN ITEMLOC IL ON IM.ITEM = IL.ITEM     LEFT JOIN ICITEUF IC ON IM.ITEM_GROUP = IC.ITEM_GROUP AND         IM.ITEM = IC.ITEM
WHERE IL.COMPANY = '3245' AND
    IL.LOCATION IN ('STOREROOM', 'WAREHOUSE') AND     IM.ITEM_GROUP = 'BHMSH'; Thanks for the answers

Charlie Received on Tue Oct 11 2011 - 09:23:21 CDT

Original text of this message