I only just started working with SQL a few weeks ago and I’ve understood and implemented joins correctly until now – It’s not my database and I don’t have access to change anything but I can query the db. Say for this example I had a table called ‘Persons’, and I got the ID from it, another table called Cases – which has the number of cases (for each person) and has an ID and the date that the case was made, and another table called PersonsCases – which has an ID and a source column (shows how the person found the site). Now, if I join them together and I get most results that I want, except that source and caseDateCreated returns either the wrong value or NULL if the person has no cases.

Sorry if I haven’t explained the question very well, since it is my first time doing this but please let me know if you need any more details on it.

TLDR: If one table is missing a value, I need to get it from another instead (I am already joining them) – ..yet it is returning the wrong value or NULL.
Here is the code I have so far:

SELECT Persons.ID as PersonID, Cases.ID as CaseID, count(activeCases.ID) NoOfCases, PersonsCases.Source, cast(Cases.CreateDateTime as date) as CaseDateCreated

from Persons

left join (select * from Cases where Cases.Active = 1) as activeCases on activeCases.PersonID = Persons.ID -- For getting the number of Cases

left join Cases ON Persons.ID = Cases.PersonID -- For joining Cases with Persons

left join PersonsCases ON Persons.ID = PersonsCases.PersonID -- For joining PersonsCases with Persons

group by Persons.ID, Cases.ID, activeCases.ID, PersonsCases.Source, Cases.CreateDateTime

order by Persons.ID

Also, know that both tables Persons and Cases have a key called SourceKey, but I’ve tried joining them with that and it hasn’t worked.