JOINing problems

[johnnyb]

Hello,

I'm having a problem with a join I can't figure out. I have 3 tables - profession, ballet_creator, and person involved here. I want to select all professions, and if there is a person referenced through ballet_creator I want to select those people as well.

Here's my code as is:

SELECT
profession.*,
ballet_creator.*,
CONCAT_WS(' ', person.FIRST_NAME, person.MIDDLE_NAME, person.LAST_NAME) AS PERSON_NAME
FROM profession
JOIN ballet_creator ON ballet_creator.PROFESSION_ID = profession.PROFESSION_ID
JOIN person ON person.PERSON_ID = ballet_creator.PERSON_ID
WHERE profession.PROFESSION_ID > 1
AND profession.PROFESSION_ID <> 8
ORDER BY PRO_DISP_ORDER, BC_DISP_ORDER


The problem is that it is returning a result set for everyone who has worked on any ballet in the database, (the ballet table is also referenced by ballet_creator). I want to return ALL professions (except the couple I excluded in the WHERE clause), and then leave the rest of the fields in the result set blank unless there are people doing that job...

An example set would be:

Prof_ID: |Prof_name: |Person_ID:|Person_Name: |
------------------------------------------------------
1 |Choreographer |1 | Petipa |
1 |Choreographer |2 | Ivanov |
2 |Composer |4 | Tchaikovsky |
3 |Set Designer |NULL | NULL |
4 |Light Designer|NULL | NULL |
------------------------------------------------------


However, I can't seem to get it to work - any ideas? or What should my SQL look like?


Thanks,

John
PS. Enjoy my ASCII art for the table!

[johnnyb]

So, after all that work on the result table I found the answer on my own. I added an extra condition to the LEFT JOIN condition.

It looks like this:


SELECT
profession.*,
ballet_creator.*,
CONCAT_WS(' ', person.FIRST_NAME, person.MIDDLE_NAME, person.LAST_NAME) AS PERSON_NAME
FROM profession
LEFT JOIN ballet_creator
ON (ballet_creator.PROFESSION_ID = profession.PROFESSION_ID
AND ballet_creator.BALLET_ID = ".$ballet_id.")
LEFT JOIN person ON person.PERSON_ID = ballet_creator.PERSON_ID
WHERE profession.PROFESSION_ID > 1
AND profession.PROFESSION_ID <> 8
ORDER BY PRO_DISP_ORDER, BC_DISP_ORDER


Thanks for the read. If anyone knows a better way to do this, (or why this will screw me up in the future), let me know.

John