SQL Query

From: Eric Fortin <emfortin_at_earthlink.net>
Date: Fri, 08 Feb 2002 17:14:12 GMT
Message-ID: <EHT88.21705$3E5.1740694@newsread2.prod.itd.earthlink.net>

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 Received on Fri Feb 08 2002 - 11:14:12 CST