5
SELECT count(*) FROM projects, project_images WHERE projects.project_id = project_images.project_id AND project_type=1
Unfortunaltely that doesn't work when more images belong to one project. In that case that query would effectively count the number of images belonging to all projects instead of all the projects that have at least one image belonging to them.
I have been able to solve this problem for the actual retrieval of the projects that have at least one image belong to them by doing this:
SELECT DISTINCT projects.project_id AS project_id, project_name, project_description, project_type, project_location, project_details, project_status, project_salesinfo, project_rentinfo, project_enrollment, project_areacode
FROM projects, project_images
WHERE projects.project_id=project_images.project_id
AND project_type=1
ORDER BY project_name
This example works cause the image_id field is not included in the SELECT and therefore generates duplicate records for each image belonging to a project. DISTINCT will get rid of these.
But not when using something like:
SELECT DISTINCT count(projects.project_id) FROM projects, project_images WHERE projects.project_id = project_images.project_id AND project_type=1
In this case MySQL first basically counts all the records when combining the two tables which generates a record for each image linked to a project. That results in a single record and last MySQL applies DISTICT which by then is naturally too late since there is but one record holding a field with an incorrect project count.
This problem is really nagging me. Tried all kinds of JOINS to solve it but haven't come up with any solution. Maybe I'm missing something.
