Home » SQL & PL/SQL » SQL & PL/SQL » Questions on nested select statements
Questions on nested select statements [message #197126] Tue, 10 October 2006 03:13 Go to next message
stevefox777
Messages: 26
Registered: September 2006
Junior Member
Hi,
I need some help with nested select statements.
I have two tables below table1 and table2.

select * from table1;

ID        Product_type
------ ------------
001      CAR
002      BIKE
003      BOAT


select * from table2;


Product_name     ID
-------------   ------
AAA                  002EEE
BBB                  001FFF
DDD                  003GGG



I want an sql command which will find the product name from the product type.
I can do this by running two sql commands below but I want to do this in a single SQL command.

select ID from table1 where Product_type='BIKE';

ID
------
002

select Product_name from table2 where ID like '002%';

Product_name
-------------
AAA


I tried to do this in a single sql command like below but I couldn't find out how to insert an wildcard character to the result of the nested select statement.

select Product_name from table2 where ID like '(select Product_name from table2 where ID like '002%')%';



Any help will be appreciated.

Steve
Re: Questions on nested select statements [message #197130 is a reply to message #197126] Tue, 10 October 2006 03:27 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

try this .
select product_name from table2 t2 where exists
(select null from table1 t1 where substr(t2.id,1,3)=t1.id and t1.product_type='BIKE')




regards,
Re: Questions on nested select statements [message #197134 is a reply to message #197130] Tue, 10 October 2006 03:49 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Here's two ways of doing it:
create table temp_prod_1 (id  varchar2(3), product_type  varchar2(30));

create table temp_prod_2 (product_name  varchar2(5), id varchar2(6));


insert into temp_prod_1 values ('001','CAR');
insert into temp_prod_1 values ('002','BIKE');
insert into temp_prod_1 values ('003','BOAT');


insert into temp_prod_2 values ('AAA','002EEE');
insert into temp_prod_2 values ('BBB','001FFF');
insert into temp_prod_2 values ('DDD','003GGG');

select t2.product_name
from   temp_prod_1 t1
      ,temp_prod_2 t2
where  substr(t2.id,1,3) = t1.id
and    t1.product_type = 'CAR';

select t2.product_name
from   temp_prod_1 t1
      ,temp_prod_2 t2
where  t2.id like t1.id||'%'
and    t1.product_type = 'CAR';
Re: Questions on nested select statements [message #197135 is a reply to message #197126] Tue, 10 October 2006 03:50 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm sure we don't need to point out that your data model has a huge flaw in it.....
Re: Questions on nested select statements [message #197136 is a reply to message #197126] Tue, 10 October 2006 03:51 Go to previous message
stevefox777
Messages: 26
Registered: September 2006
Junior Member
Thanks dhananjay!

I was able to do it with:

select product_name from table2 t2 where substr(t2.id,1,3) in (select t1.ID from table1 t1 where Product_type='BIKE');


Cheers
Steve
Previous Topic: Question on sum up of 2 columns that created by "decode" function
Next Topic: passing recorset as a parameter
Goto Forum:
  


Current Time: Wed Dec 07 10:51:08 CST 2016

Total time taken to generate the page: 0.06303 seconds