I have a query that I cannot get to work right. I have 3 tables; Person, PersonProgram and Category.
Person: ID, ....
PersonProgram: ID, PersonID, Category, Code ...
Category: ID, ...
The Person table has 1 record for each person and the PersonProgram has multiple programs per person. There are 4 categories and I need to pull into a single row, of each person, with specific Program of each category.
Person Table:
1
2
3
PersonProgram Table
1, 1, 1, 1
2, 1, 2, 1
3, 1, 1, 3
4, 2, 1, 1
5, 2, 3, 3
What the desired outcome should be:
PersonID, ProgramIDforCat1, ProgramIDforCat2, ProgramIDforCat3, ProgramIDforCat4
1, 1, 2, NULL, NULL
2, 1, NULL, 3, NULL
The problem is that there is multiple Program records for each person and category with a code of 1, 2 or 3. I need to put priority on Code 1 then Code 3 and ignore the rest, while still only pulling 1 record, or NULL if it does not exist.
I am losing it trying to get this to work.
FYI, it has to be in a view.
Thanks for any help.