Select statement

[Leon1977]

hi I need help with an select statement
I have two table Stores and sells
so in the Stores table i have store_id and store_name
and in sells i have from_store_id and to_store_id
Both from_store_id and to_store_id refer store_id from stores. I need a single select statement which will bring out the store_names for both from and to stores

 

[jee]

You simply need to do two joins - that is treat it like you have two STORES tables - one for "from" and one for "two":

SELECT to.store_name, from.store_name
FROM sells, stores to, stores from
WHERE to.store_id = sells.to_store_id
AND from.store_id = sells.from_store_id
AND and other selection criteria you want

 

[sem]

select f.store_name from_name,
t.store_name to_name
--,<columns from sells>
from Stores f, sells s, stores t
where s.from_store_id = f.store_id
and s.to_store_id = t.store_id

 

[ija]

select froms.store_name as from_store,
tos.store_name as to_store
from stores froms,
stores tos,
sells int
where froms.store_id = int.from_store_id
and int.to_store_id = tos.store_id

 

[ija]

It looks like we three are writing response at the sime time - funny...