CODEWITHSUNDEEP
sqlsql-serverjoinsubquery
Raj
Raj

Reputation: 1965

Replace subquery with join for the same table

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

Answers (3)

Dzejki
Dzejki

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

Nick
Nick

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

SQL Server demo on SQLFIddle

Oracle demo on SQLFiddle

Upvotes: 2

Gordon Linoff
Gordon Linoff

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

Related Questions