CODEWITHSUNDEEP
sqljoin
user175084
user175084

Reputation: 4640

combine 2 queries

I have two queries which i would like to combine There are 4 tables which are linked. user, group, policy, source

1st query i get the users with the respective policy id.

1st Query:

Select users.ID as UserID,users.Username,   
policy.ID as PolicyID,policy.PolicyName 
from users join groups on group.ID = user.groupID_FK 
join policy on users.policyID_FK = policy.ID

2nd Query: Here i need to match the policyID_FK and apply the path. the source type should be 0, it is possible that there is no entry of datasource type o for that policyID_FK .

Select Path, policyID_FK from source
WHERE SourceType = 0 and policyID_FK  = 1

Here instead of giving policyID_FK = 1 i want to join this query with the above query.

Please help. I know i am not much clear but please ask if you don't understand something.

Thanks

Upvotes: 0

Views: 128

Answers (2)

Nikola Markovinović
Nikola Markovinović

Reputation: 19356

Select users.ID as UserID,users.Username, policy.ID as PolicyID,policy.PolicyName, source.Path
from users join groups on group.ID = user.groupID_FK 
   join policy on group.policyID_FK = policy.ID
   left join source
      on group.policyID_FK = source.policyID_FK
      and SourceType = 0

Use left join when you know that right table might not have matching records. Append any filter you have beside join to left-join filter, because if you put it in where it will remove rows you want to retrieve.

Upvotes: 2

John Woo
John Woo

Reputation: 263933

Is this what you want?

Select  users.ID as UserID,
        users.Username,   
        policy.ID as PolicyID,
        policy.PolicyName,
        source.Path
FROM    users INNER JOIN groups 
            ON group.ID = user.groupID_FK 
        INNER JOIN policy 
            ON users.policyID_FK = policy.ID
        INNER JOIN source 
            ON users.policyID_FK = source.policyID_FK
-- You can add conditions here

Upvotes: 1

Related Questions