CODEWITHSUNDEEP
sqlsql-server
ND's
ND's

Reputation: 2215

Join tables based on a parameter

In my database I have the following three tables with the fields listed:

TablePS 

 aID 

TableAM 

 aID(pk)
 bID  

TableM 

 bID(pk)
 bName

If I know the aID in TablePS, how can I find the value of bName in TableM? I have tried the following query:

SELECT bName FROM TableM WHERE bID=
(SELECT TablePS.aID,TableAM.aID 
FROM TablePS 
INNER JOIN TableAM 
ON TablePS.aID= TableAM.aID)

Upvotes: 1

Views: 91

Answers (4)

whytheq
whytheq

Reputation: 35557

If you don't want to use JOINs then maybe this.

I've assumed aId is type INT which you can change.

declare @x as int = 10 --<<change to an id that you are searching for

 select bname from  TableM  where bid in 
            (
            select  bID from TableAM  
            where aID in 
                             (
                             select aID from TablePS where aId = @x
                             ) 
            )

Upvotes: 0

Steve
Steve

Reputation: 216263

SELECT k.bName 
FROM TablePS p 
     INNER JOIN TableAM m ON m.aID = p.aID
     INNER JOIN TableM k on k.bID = m.bID

Upvotes: 1

Madhivanan
Madhivanan

Reputation: 13700

Select BName from TableM as M inner join TableAM as Am on M.bId=Am.bID
inner join TablePS as P on Am.Aid=P.Aid

Upvotes: 1

podiluska
podiluska

Reputation: 51494

select bName
from tableM
    inner join tableAM on tableM.bID = tableAM.bID
where
    aID = [value]

should be sufficient. If you need to join to table PS, then

select bName
from tableM
    inner join tableAM on tableM.bID = tableAM.bID
    inner join tablePS on tableAM.aID = tablePS.aID

Upvotes: 4

Related Questions