CODEWITHSUNDEEP
sqljoin
Kraz
Kraz

Reputation: 7090

How to do a sql conditional joining on multiples tables?

I have an already existing SQL request that join 8 tables togethers, I need to add 3 mores tables to add a facultative information to my data.

For this task, we can ignore 6 tables (bundled in other_tables) :

db schema

The part in the red box is the one I got to add. I want to only add request that respect a few conditions. If none are found, I still want my person, but with no data in the Request column.

So far I tried this, but the multiple left join seems ugly and too inclusive.

Select [...], request.Id
From [Action] inner join [other tables on ...] inner join
    Person on Action.PersonId = Person.Id left JOIN 
request as r on r.PersonId = Person.Id left JOIN
Subject as s on s.SubjectId = r.SubjectId left JOIN
TypeRequest tr on tr.IDSujet = s.SubjectId
Where 
([already existing conditions]) and typeRequest = 'MOVE' (?)

And to rephrase once more what I want : Get me all actions according to some filter (on other tables), also get the person they are linked to. If that person has a Request of a specific TypeRequest, add the Request id to the data row.

Upvotes: 1

Views: 61

Answers (1)

b3ko
b3ko

Reputation: 390

Does the query above work? "seems ugly and too inclusive" is a bit vague. are you getting the results you want? you can also move part of the where clause to the join like this:

    Select [...], request.Id
From [Action] inner join [other tables on ...] inner join
    Person on Action.PersonId = Person.Id left JOIN 
request as r on r.PersonId = Person.Id left JOIN
Subject as s on s.SubjectId = r.SubjectId left JOIN
TypeRequest tr on tr.IDSujet = s.SubjectId **and typeRequest = 'MOVE'**
Where 
([already existing conditions])

Upvotes: 1

Related Questions