Re: many to many LEFT JOIN

[Roland Bouman]

Well, i think Callum suggestion was right.

What i think's been bugging you is the = for the project_id thing.

If you just do just this, do you get any records at all?

SELECT nis_store.id_store
, nis_project.id
, nis_project.billingcode
, nis_project.subject
, nis_completion.comments
, nis_completion.completed
, nis_completion.na
, nis_completion.project_id
FROM nis_store
LEFT JOIN nis_completion
ON nis_store.id_store = nis_completion.store_id
LEFT JOIN nis_project
ON nis_completion.project_id = nis_project.id
ORDER BY nis_store.id_store

Yes? then it's definitely the = $_GET[] stuff bugin you. Why? say, you would have a store without any completion, then the LEFT JOIN operation would generate null records for both the completion and the store tables ,yes? Then, asking MySQL if these nulls are equal to the $_GET stuff will never get any of those NULL records, because NULL = something is never true.

My guess is, that when you would ask the question the other way around that you would get the desired results:

SELECT nis_store.id_store
, nis_project.id
, nis_project.billingcode
, nis_project.subject
, nis_completion.comments
, nis_completion.completed
, nis_completion.na
, nis_completion.project_id
FROM nis_project
LEFT JOIN nis_completion
ON nis_completion.project_id = nis_project.id
LEFT JOIN nis_store
ON nis_store.id_store = nis_completion.store_id
WHERE nis_project.id = somevalue

Pls let us know