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?

On Oct 10, 2:14*pm, cburs... (AT) geusnet (DOT) com wrote:
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
--
@home.com is bogus.
http://www.10news.com/news/29435424/detail.html

On 10.10.2011 23:14, cbursell (AT) geusnet (DOT) com wrote:
What if there is more than one location but no WAREHOUSE location?

See Joel's reply.

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

On Oct 11, 1:03*am, Robert Klemme <shortcut... (AT) googlemail (DOT) com> wrote:
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

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 A432

On Oct 11, 7:23*am, cburs... (AT) geusnet (DOT) com wrote:

Yes. If it is easy for people to set up a test example, then they can
try it and play with it rather than guess or ignore it (most will
ignore it). It also means you can better determine whether your
simplification makes sense as you see what you haven't told people.

The correct answer is, of course, normalize your design. If for
whatever reason you are stuck with it, usually a sql answer will be
the best, although that assumes at least third normal form. If you
can't do SQL, use PL/SQL. Other languages would be below that. (I'm
stuck with a 4GL that often fights with oracle, by assuming version
7. When I see problem statements like "There will always be at least
one location which will be either Warehouse or Storeroom." I have to
question how that is enforced. Enterprise inventory systems will
often have strange requirements.)

A distinct often leads to a sort, so if it can be avoided, that is
usually better. The merits of ansi versus oracle style join syntax
are debatable, but you might want to watch for version-dependent bugs.

jg
--
@home.com is bogus.
http://www.technewsworld.com/story/c...ary/73432.html

cbursell (AT) geusnet (DOT) com wrote:

with x as (
select
t.*,
row_number() over (partition by item order by
decode(location,'WAREHOUSE',2,'STOREROOM',1,0) desc) rnk
from t
) select * from x where rnk = 1;

cheers,
geos

On Oct 11, 11:10*am, joel garry <joel-ga... (AT) home (DOT) com> wrote:
As you may have guessed, not my design, I have to live with it

I can do it via ODBC and Tcl but was looking for a pure SQL answer as
well.

Thanks for the help

Charlie

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?

----------

With those conditions in mind a simple grouping and a MAX function on the
LOCATION field would always ensure that WAREHOUSE would be selected over
STOREROOM, and thus two possible simple solutions comes to (my) mind.

SELECT ITEM, DESCRIPTION, LOCATION, BIN
FROM <TABLE> T1
WHERE LOCATION = (SELECT MAX(LOCATION) FROM <TABLE> WHERE ITEM=T1.ITEM AND
DESCRIPTION=T1.DESCRIPTION)

OR

SELECT T1.ITEM, T1.DESCRIPTION, T1.LOCATION, T1.BIN
FROM <TABLE> T1
INNER JOIN (
SELECT ITEM, DESCRIPTION, MAX(LOCATION) AS LOCATION
FROM <TABLE>
GROUP BY ITEM, DESCRIPTION
) T2 ON T1.ITEM=T2.ITEM AND T1.DESCRIPTION=T2.DESCRIPTION AND
T1.LOCATION=T2.LOCATION