Problem with SELECT

[Fly]

Table 'Items' is such this:

ItemID int
ItemType varchar

Select * From Items:

ItemID ItemType
---------------------
10 motors
16 cars
12 motors

Each ItemID is taken from different table: the first ItemID is taken from table 'motors', second is from table 'cars'... Now I want to display names of these items, not ItemID. These names are in each table such 'motors' and 'cars', for example: MotorName and CarName. How can I do it?

Fly

[HemantH]

The best way to do it is to have a lookup table or an other column in the item table that suites your description.


------------
Fly at 8/22/00 11:27:18 AM

Table 'Items' is such this:

ItemID int
ItemType varchar

Select * From Items:

ItemID ItemType
---------------------
10 motors
16 cars
12 motors

Each ItemID is taken from different table: the first ItemID is taken from table 'motors', second is from table 'cars'... Now I want to display names of these items, not ItemID. These names are in each table such 'motors' and 'cars', for example: MotorName and CarName. How can I do it?

Fly

[Fly]

propably my question was uncelar. I have only ONE table for orders and several tables with Items (motors, cars, something). And now from this 'orders table' I want to know about items names (I now only ItemsID from these tables now)...

fly

------------
HemantH at 8/22/00 11:33:41 AM

The best way to do it is to have a lookup table or an other column in the item table that suites your description.


------------
Fly at 8/22/00 11:27:18 AM

Table 'Items' is such this:

ItemID int
ItemType varchar

Select * From Items:

ItemID ItemType
---------------------
10 motors
16 cars
12 motors

Each ItemID is taken from different table: the first ItemID is taken from table 'motors', second is from table 'cars'... Now I want to display names of these items, not ItemID. These names are in each table such 'motors' and 'cars', for example: MotorName and CarName. How can I do it?

Fly

[HemantH]

As per my understanding you have the ITEMSID in ORDERS table, but there are several ITEM tables and you can't figure out to which table to go.
If this is the case, have an other column in ORDERS table which will indicate which ITEM table to query to get the Item name.

An alternative is to create a view merging all the ITEM tables and querying on the view. This requires ITEM_ID to be unique in all tables.

------------
Fly at 8/22/00 11:46:38 AM

propably my question was uncelar. I have only ONE table for orders and several tables with Items (motors, cars, something). And now from this 'orders table' I want to know about items names (I now only ItemsID from these tables now)...

fly

------------
HemantH at 8/22/00 11:33:41 AM

The best way to do it is to have a lookup table or an other column in the item table that suites your description.


------------
Fly at 8/22/00 11:27:18 AM

Table 'Items' is such this:

ItemID int
ItemType varchar

Select * From Items:

ItemID ItemType
---------------------
10 motors
16 cars
12 motors

Each ItemID is taken from different table: the first ItemID is taken from table 'motors', second is from table 'cars'... Now I want to display names of these items, not ItemID. These names are in each table such 'motors' and 'cars', for example: MotorName and CarName. How can I do it?

Fly

[Carmelo Scarpino]

All I can think of is to outer join to each of the 3 tables. The one that matches will join. The only problem I see is that if the item id's among the 3 files are not unique you will get wierd results.


------------
Fly at 8/22/00 11:27:18 AM

Table 'Items' is such this:

ItemID int
ItemType varchar

Select * From Items:

ItemID ItemType
---------------------
10 motors
16 cars
12 motors

Each ItemID is taken from different table: the first ItemID is taken from table 'motors', second is from table 'cars'... Now I want to display names of these items, not ItemID. These names are in each table such 'motors' and 'cars', for example: MotorName and CarName. How can I do it?

Fly

[Roopdaska]

Hi,
If iam not wrong,If you have item names in ordertable then
you can use the following sql

selec o.itemname from orders o,Item I where o.ItemID=I.Itemid

You can select whatever columns you want by mentioning them in the select list

------------
Fly at 8/22/00 11:46:38 AM

propably my question was uncelar. I have only ONE table for orders and several tables with Items (motors, cars, something). And now from this 'orders table' I want to know about items names (I now only ItemsID from these tables now)...

fly

------------
HemantH at 8/22/00 11:33:41 AM

The best way to do it is to have a lookup table or an other column in the item table that suites your description.


------------
Fly at 8/22/00 11:27:18 AM

Table 'Items' is such this:

ItemID int
ItemType varchar

Select * From Items:

ItemID ItemType
---------------------
10 motors
16 cars
12 motors

Each ItemID is taken from different table: the first ItemID is taken from table 'motors', second is from table 'cars'... Now I want to display names of these items, not ItemID. These names are in each table such 'motors' and 'cars', for example: MotorName and CarName. How can I do it?

Fly

[Fly]

Yes, all ItemID are not unique. So I think Roopdaska anwer will works good. I have to name ItemType properly to table names only. Thanx

fly


------------
Carmelo Scarpino at 8/22/00 11:56:44 AM

All I can think of is to outer join to each of the 3 tables. The one that matches will join. The only problem I see is that if the item id's among the 3 files are not unique you will get wierd results.


------------
Fly at 8/22/00 11:27:18 AM

Table 'Items' is such this:

ItemID int
ItemType varchar

Select * From Items:

ItemID ItemType
---------------------
10 motors
16 cars
12 motors

Each ItemID is taken from different table: the first ItemID is taken from table 'motors', second is from table 'cars'... Now I want to display names of these items, not ItemID. These names are in each table such 'motors' and 'cars', for example: MotorName and CarName. How can I do it?

Fly

[Fly]

Roopdaska way was not too good. I will try explain my problem once again:

Items (orders) table:

ItemID ItemType
---------------------
10 motors
10 cars
12 motors

Motors table:
MotorID
MotorName

Cars table:
CarID
CarName

So, in Items table we have 2 motors and 1 car. MotorsID are 10 and 12. CarID is 10. MotorNames can be for example Yamaha 232, Suzuki 2111 and CarName is BMW 525. Now I want to list Items(my orders) table in such way:

ItemName(or something)
--------
Yamaha 232
BMW 525
Suzuki 2111

Is it clear now?

Fly

[Roopdaska]

hI ,

i WAS NOT CLEAR ABOUT YOUR PROBLEM.

TRY THE FOLLOWING SQL.IT SHOULD WORK



select distinct MotorName from motors m,Items i where i.ItemId=m.MotorID
UNION
SELECT distinct CarName from cARS C,Items i where i.ItemId=C.CarID

------------
Fly at 8/22/00 12:13:11 PM

Yes, all ItemID are not unique. So I think Roopdaska anwer will works good. I have to name ItemType properly to table names only. Thanx

fly


------------
Carmelo Scarpino at 8/22/00 11:56:44 AM

All I can think of is to outer join to each of the 3 tables. The one that matches will join. The only problem I see is that if the item id's among the 3 files are not unique you will get wierd results.


------------
Fly at 8/22/00 11:27:18 AM

Table 'Items' is such this:

ItemID int
ItemType varchar

Select * From Items:

ItemID ItemType
---------------------
10 motors
16 cars
12 motors

Each ItemID is taken from different table: the first ItemID is taken from table 'motors', second is from table 'cars'... Now I want to display names of these items, not ItemID. These names are in each table such 'motors' and 'cars', for example: MotorName and CarName. How can I do it?

Fly