Re: Query to extract one record from multiple records
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