Left Join with where clause

Question

I have 3 tables A, B and C

I am doing this :- /* There is no relation between Table A and Table B . Table A is just used to provide values to C.Profile */ 1st step ) D <---- Select * from C where Profile=1 // want to give a specific ProID(I have successfully retrieved it from A table)

2nd Step ) Output <--- Select B.sname,D.Status from B Left Join D On B.ID=D.ID

so that the output looks like the required output table shown above:-

Can I do this by using a single query? how?

Answer 1

Do you mean a subquery:

Select B.DirName,D.Status 
from B Left Join (
    Select * 
    from C 
    where ProId=1) As D 
On B.DirID=D.DirID

It is best to use a list of fields rather than *