Re: SQL Query

Try:

select item_number,

     (select MAX(f.flag_code) AS value
        from tmp_item_flags if,
             tmp_flags f
       where i.item_id = if.item_id
         and f.flag_id = if.flag_id
         AND f.flag_code = 'FLAG A') AS flag_a,  
     (select MAX(f.flag_code) AS value
        from tmp_item_flags if,
             tmp_flags f
       where i.item_id = if.item_id
         and f.flag_id = if.flag_id
         AND f.flag_code = 'FLAG B') AS flag_b,  
     (select MAX(f.flag_code) AS value
        from tmp_item_flags if,
             tmp_flags f
       where i.item_id = if.item_id
         and f.flag_id = if.flag_id
         AND f.flag_code = 'FLAG C') AS flag_c
from tmp_items i
      

The result concerning item_number "ITEM C" is different from what you asked for.

I think there is a bug either in yout INSERTs or your expected result set.

On Fri, 08 Feb 2002 17:14:12 GMT, "Eric Fortin" <emfortin_at_earthlink.net> wrote:

>I hope this is the right place.
>I'm using 8.1.7 on NT
>
>I have 3 tables
>
>INVENTORY_ITEMS
>-------------------
>ITEM_ID NUMBER
>ITEM_NUMBER VARCHAR2(25)
>
>INVENTORY_FLAGS
>--------------------
>FLAG_ID NUMBER
>FLAG_CODE VARCHAR2(25)
>
>ITEM_FLAGS
>--------------------------
>ITEM_ID
>FLAG_ID
>
>I'm trying to retrieve a cursor that will return the following:
>
>Item Number | Flag Code Value | Flag Code Value | Flag Code Value
> ---------------------------------------------------------------------------
>-------------------
>Item X null null
>null
>Item Y null True
>true
>Item Z True null
>true
>
>
>I've searched a couple of SQL books, but did not see an immediate answer;
>I know that it is possible via pl/sql but would prefer a single statement.
>
>
>I tried to create a couple of very simple scripts (as the real tables are a
>bit more complex), if someone can help
>
>create table tmp_items (item_id number primary key, item_number
>varchar2(10));
>create table tmp_flags (flag_id number primary key, flag_code varchar2(10));
>create table tmp_item_flags(item_id number, flag_id number);
>
>insert into tmp_items values(1, 'ITEM A');
>insert into tmp_items values(2, 'ITEM B');
>insert into tmp_items values(3, 'ITEM C');
>
>INSERT INTO TMP_FLAGS VALUES (10, 'FLAG A');
>INSERT INTO TMP_FLAGS VALUES (11, 'FLAG B');
>INSERT INTO TMP_FLAGS VALUES (12, 'FLAG C');
>
> INSERT INTO TMP_ITEM_FLAGS VALUES (2, 10);
> INSERT INTO TMP_ITEM_FLAGS VALUES (2, 11);
> INSERT INTO TMP_ITEM_FLAGS VALUES (3, 11);
>
>I would like the query to return:
>
>ITEM_NUMBER | FLAG A | FLAG B |
>FLAG C
>---------------
>ITEM A NULL NULL
>NULL
>ITEM B FLAG A FLAG B
>NULL
>ITEM C FLAG A NULL
>FLAG C
>
>
>TIA
>Eric
>--
>Eric Fortin
>303.748.9885
>EMFortin_at_pcisys.net
>
>
>
>
>

regards
Marc Blum
mailto:marc_at_marcblum.de
http://www.marcblum.de Received on Tue Feb 12 2002 - 14:31:31 CST