MySQL join problem

zeb

I have small dilemma... I have two tables linked together: images and pages. The images table has a field named pageID that links to the id field in the pages table. I need to write a query that will give me a result set detailing how many images are linked to each page AND how many are not linked to a page at all (either because the pageID doesn't match the id field in the pages table or because the pageID field is NULL.

Here is what I have so far:

SELECT
	p.id,
	p.name,
	count( i.id ) as 'count'
FROM pages p
LEFT JOIN images i
ON i.pageID = p.id
GROUP BY p.id
ORDER BY p.name

This gives me the number of images linked to each page (and 0 if there are no images linked to a page - which is important), but it doesn't include a number for all the images that are linked to a non-existent page or have a NULL pageID value.

The same syntax with a RIGHT JOIN instead of a LEFT JOIN will give me what I need for NULL and non-existent pages, but it won't show me any pages that have 0 images linked to them.

Is there a way to combine the results of these two queries into one query? It needs to run on MySQL 3.23.49.

Sxooter

Try something like

select * from ltable l left join rtable r on (l.id=r.lid) where r.lid is null

zeb

Originally posted by Sxooter
Try something like

select * from ltable l left join rtable r on (l.id=r.lid) where r.lid is null

That query only gives me pages that are not linked by any images. I need the result set to include more than that.
For example:

id    name    count
NULL  NULL    7
1     name1   4
2     name2   9
3     name3   2
4     name4   0

This set includes the number of images that are linked to each page AS WELL AS the number of images that do not link to any page.

There must be a way to get this in one query.