CODEWITHSUNDEEP
sql
Nikunj Dhimmar
Nikunj Dhimmar

Reputation: 13

SQL query for retrieve data from three tables

I have three tables:

tbl_ProjectsUsers

ProjectId
companyUserID

tbl_Projects

ProjectId
CompanyClientId

tbl_CompanyClient

CompanyClientName
CompanyClientId

How can I retrieve ProjectID,CompanyClientID,CompanyClientName from three tables Using @companyUserID

Upvotes: 0

Views: 53

Answers (3)

Dheebs
Dheebs

Reputation: 408

Hope this helps

Here is the Solution you Requested:

Declare @CompanyUserID   int = 10       --no idea what the data type is but i assume an integer.

Select
    u.CompanyUserID
,   p.ProjectID
,   c.CompanyClientID
,   c.CompanyClientName
FROM
            tbl_ProjectUsers  u
    JOIN    tbl_Projects      p   on u.ProjectID = p.ProjectID
    JOIN    tbl_CompanyClient c   on p.CompanyClientID = c.CompanyClientID
WHERE
    u.CompanyUserIDin (@CompanyUserID)

If you change the Variable number (10) it will return different results. I assume you have it set as a Int but it could be a string if it is a string something like the following should work

Declare @CompanyUserID varchar(max) = 'UserID'

You will need to determine the join types more specifically but in T-SQL this works for me. I would recommend eithier Inner Joins or Left Joins for this one, however using just JOIN will work sufficiently

POST NOTE - I see you updated your post so now it makes sense for the 3 tables

Upvotes: 0

vivek kv
vivek kv

Reputation: 416

select 
    a.ProjectId, b.CompanyClientId, c.CompanyClientName 
from 
    tbl_ProjectsUsers as a 
inner join 
    tbl_Projects as b on a.ProjectId = b.ProjectId
inner join 
    tbl_CompanyClient as c on b.CompanyClientId = c.CompanyClientId 
where 
    a.companyUserID = id

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

You can do a join between the three tables:

SELECT t1.ProjectId,
       t1.companyUserID,
       COALESCE(t2.CompanyClientId, 'NA'),
       COALESCE(t3.CompanyClientName, 'NA')
FROM tbl_ProjectsUsers t1
LEFT JOIN tbl_Projects t2
    ON t1.ProjectId = t2.ProjectId
LEFT JOIN tbl_Company_Client t3
    ON t2.CompanyClientId = t3.CompanyClientId
WHERE t1.companyUserID = <some_value>

Upvotes: 2

Related Questions