SQL Query - SQL Developer

Question

I have a problem with the below query. Basically the query below gives me all items from a ITEM_MASTER table, that are located at location '9999' from the ITEM_LOCATION table and that have a status of 'C' again in the ITEM_LOCATION table. I want to check if any of these items in the query below are also at any other location and have a status of 'A'.

So basically I want too cross reference the items from this query, to see if any of them also appear at any other location, not just 9999 and if they have a status of 'A'

SELECT IM.ITEM MIN,
   IM.ITEM_DESC,
   IL.ITEM MIN,
   IL.LOC,
   IL.STATUS
   FROM ITEM_MASTER IM,ITEM_LOC IL
   WHERE IM.ITEM_LEVEL = 2
   AND   IM.TRAN_LEVEL = 2
   AND IL.STATUS = 'C'
   AND IM.ITEM = IL.ITEM
   AND IL.LOC = 9999;

Thanks!

Gordon Linoff · Accepted Answer · 2015-05-11 13:28:11Z

5

First, you should write your query using proper join syntax:

SELECT IM.ITEM MIN, IM.ITEM_DESC,
       IL.ITEM MIN, IL.LOC, IL.STATUS
FROM ITEM_MASTER IM JOIN
     ITEM_LOC IL
     ON IM.ITEM = IL.ITEM
WHERE IM.ITEM_LEVEL = 2 AND IM.TRAN_LEVEL = 2 AND IL.STATUS = 'C' AND
      IL.LOC = 9999;

You can accomplish what you want with exists:

SELECT IM.ITEM MIN, IM.ITEM_DESC,
       IL.ITEM MIN, IL.LOC, IL.STATUS
FROM ITEM_MASTER IM JOIN
     ITEM_LOC IL
     ON IM.ITEM = IL.ITEM
WHERE IM.ITEM_LEVEL = 2 AND IM.TRAN_LEVEL = 2 AND IL.STATUS = 'C' AND
      IL.LOC = 9999 AND
      EXISTS (SELECT 1
              FROM ITEM_MASTER IM2 JOIN
                   ITEM_LOC IL2
                   ON IM2.ITEM = IL2.ITEM
              WHERE IM2.ITEM = IM.ITEM AND
                    IL2.LOC <> 9999 AND
                    IL2.STATUS = 'A'
             );

edited May 11, 2015 at 13:28

answered May 11, 2015 at 12:52

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Mark Leslie Newsham-Kent Over a year ago

I've been told to not use JOINS and to use Allas' instead

Raphaël Althaus Over a year ago

+1. Just two things : the im2.Loc <> 9999 isn't needed, if I understand the question well (I think that only a stauts = 'A' should be checked, but I may be wrong). And the exists could maybe be in a case when in the select...

Christian Barron Over a year ago

This is the correct answer and the correct way to do it. You can still alias the tables in the joins. Although @RaphaëlAlthaus is right, you don't need the <> 9999.

Mark Leslie Newsham-Kent Over a year ago

I get the following error when I try that; ORA-00904: "IM2"."LOC": invalid identifier 00904. 00000 - "%s: invalid identifier" *Cause: *Action: Error at Line: 16 Column: 5

Gordon Linoff Over a year ago

@RaphaëlAlthaus . . . "at any other location . . . and if they have a status of 'A'". I interpret this to mean that both fields need to be different.

|

Collectives™ on Stack Overflow

SQL Query - SQL Developer

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related