Reputation: 1965
Can the expected result in this case be accomplished without a subquery ? Maybe using a join ?
We have a name say 'jose', Expected result is all rows which has same color as jose. Query should run in both MS-SQL and ORACLE.
query
======
select name,color from tableA where color=(select color from tableA where name='jose')
Expected result
===============
name color
jose red
Rap red
schema
=======
Table and DATA
create table tableA (
name varchar(10),
color varchar(10)
);
insert into tableA values ('jose','red');
insert into tableA values ('Mickey','blue');
insert into tableA values ('Leo','yellow');
insert into tableA values ('Rap','red');
insert into tableA values ('Don','blue');
http://sqlfiddle.com/#!18/5f7e3/2
Upvotes: 0
Views: 1394
Reputation: 1
I think the best way is "CTE" :
with
cte1 as (
select 1 as RowToJoin,
name,
color
from tableA
),
cte2 as (
select 1 as RowToJoin,
color
name
from tableA
where name='jose'
)
select c1.name, c1.color
from cte1 c1
join cte2 c2 on c2.RowToJoin = c1.RowToJoin
where c1.name <> c2.name
It looks as something hard, but it's simple. Try to read about it.!
Upvotes: 0
Reputation: 147286
You can get this result with a JOIN
, by self-joining on the color
field, where the name in the second table is jose
:
SELECT a1.name, a1.color
FROM tableA a1
JOIN tableA a2 ON a2.color = a1.color AND a2.name = 'jose'
Output
name color
jose red
Rap red
Upvotes: 2
Reputation: 1271241
If names have only one color, then you would seem to want:
select a.*
from tableA a
where a.color = (select a2.color from tableA a2 where a2.name = 'jose');
You might want to add another condition that a.name <> 'jose'
if you don't want to return that row.
Upvotes: 0