Board index » delphi » Select Statement help

Select Statement help


2005-11-08 03:55:05 AM
delphi266
I need to get just the list of Item Types from my Item types table (5
records only)
The Items table has a couple of fields I need to inlcude in my SQL result.
The Items table has a field named ITEM_TYPE each item in the Items tables
has a item type defination ( Handset, Battery, Charger, LCD Panel, etc)
When I run the statement below I get in my result duplicated Item_type
because each Item have their own item_type defination. How can I just get
the 2 fields I need from my items table a only the Item types list from my
Item_Types table.
please take a look at my statement below.
Tia
Charles
*********** My SQL Statement *********
SELECT DISTINCT
a.ITEM_TYPE_ID,
a.ITEM_TYPE_DESCRIPTION,
b.ITEM_ID,
b.MAKE,
b.MODEL
FROM
ITEM_TYPES a
LEFT OUTER JOIN ITEMS b ON a.ITEM_TYPE_ID = b.ITEM_TYPE
ORDER BY a.ITEM_TYPE_ID, a.ITEM_TYPE_DESCRIPTION
***************** End of Statement *******************
SQL RESULTS:
ITEM_TYPE_ID ITEM_TYPE_DESCRIPTION ITEM_ID MAKE MODEL
-----------------------------------------------------------------------------------------
1 CELLPHONE 12
7 29
1 CELLPHONE 17
10 40
2 BATTERY 13
7 29
2 BATTERY 18
10 40
and so on..
I need to get this result
ITEM_TYPE_ID ITEM_TYPE_DESCRIPTION ITEM_ID MAKE MODEL
-----------------------------------------------------------------------------------------
1 CELLPHONE 12
7 29
2 BATTERY 13
7 29
3 CHARGER
4 HOUSING
5 LCD PANEL
************* ITEM_TYPES Table **************
CREATE TABLE ITEM_TYPES (
ITEM_TYPE_ID INTEGER NOT NULL,
ITEM_TYPE_DESCRIPTION VARCHAR(40)
);
**********************
************** ITEMS Table ************
CREATE TABLE ITEMS (
ITEM_ID INTEGER NOT NULL,
ITEM_CODE VARCHAR(35),
EZ_MODEL_ID INTEGER,
ITEM_NAME VARCHAR(50),
ITEM_DESCRIPTION VARCHAR(40),
ITEM_COST NUMERIC(9,2) Default 0.00,
ITEM_PRICE NUMERIC(9,2),
ITEM_TYPE INTEGER,
MAKE INTEGER,
MODEL INTEGER,
ITEM_CONDITION_ID INTEGER,
PICTURE BLOB SUB_TYPE 0 SEGMENT SIZE 80,
ITEM_PACKAGING_TYPE VARCHAR(10),
IN_STOCK_QTY INTEGER
);
************************************
 
 

Re:Select Statement help

Assume that there are 5,000 rows in Items that have Battery as the item
type. For the one row in the result set that contains Battery as the
item type which of the 5,000 rows in Items should the Item_Id, Make and
Model come from???
--
Bill Todd (TeamB)
 

Re:Select Statement help

Bill,
I found a simpler way to accomplish my task. the way I was trying to do it
was more elegante but more complicated, I just going to have a list with all
the components fro each make and model to be display and the user will
select from the list the component they want to include in the kit.
Thanks one again for your help below is the stament I will use
charles
SELECT
ITEM_ID,
ITEM_CODE,
ITEM_NAME,
MAKE,
MODEL
FROM ITEMS
WHERE
MAKE = :aMAKE and MODEL = :aMODEL
ORDER BY ITEM_NAME, ITEM_TYPE
"Bill Todd" <XXXX@XXXXX.COM>writes
Quote
Assume that there are 5,000 rows in Items that have Battery as the item
type. For the one row in the result set that contains Battery as the
item type which of the 5,000 rows in Items should the Item_Id, Make and
Model come from???

--
Bill Todd (TeamB)