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
Advertisement
Answer
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