how to convert subquery to a join

Question

how to convert subquery to a join

skanth

Right there with Babe

Points: 730
More actions
November 7, 2011 at 9:00 am

#246337

select *
from
a,b,c
where
A.cash_id in
( select B.cash_id
from B
where B.cash_id = C.cash_id
)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Why do you want to?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

John Michael Robertson SSC Eights! Points: 988 More actions · Answer 2

skanth (11/7/2011)
select *
from
a,b,c
where
A.cash_id in
( select B.cash_id
from B
where B.cash_id = C.cash_id
)

Based on your query, all three tables (a,b, & c) link on "cash_id". So, you can just perform an inner join on each of them on this column unless there is something else not depicted in your sample.

select *

from a

join b

on b.cash_id=a.cash_id

join c

on c.cash_id=a.cash_id

Paul White SSC Guru Points: 150468 More actions · Answer 3

skanth (11/7/2011)
select *
from
a,b,c
where
A.cash_id in
( select B.cash_id
from B
where B.cash_id = C.cash_id
)

The literal translation of that query to join syntax is:

SELECT *

FROM A JOIN C ON C.id = A.id

CROSS JOIN B

WHERE EXISTS (SELECT * FROM B WHERE B.id = C.id)

It's almost certainly not what you intended (particularly the cross join, and referencing table B twice) so the join form of the query as you probably intended it is close to what John gave, depending on how duplicates and NULLs should be handled.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

skanth Right there with Babe Points: 730 More actions · Answer 4

the oledb source is not accepting parameters for sql commands with sub queries....... so i need the joins instead of subqueries..!