In my photo album software ( http://3e.org/pw/source.php ), I
currently use the following query to get a list of locations along
with the number of photos that are in that location:

SELECT   locations.loc_id, loc_name, count(pho_id) AS cnt
FROM     locations, photos
WHERE    photos.loc_id = locations.loc_id
GROUP BY loc_name

However, this does not show locations that have NO photos -- i.e., if
there is a row in locations which has no corresponding row in photos,
that location will NOT be returned by this query.

I want a query that will return ALL rows from locations, even if no
corresponding row in photos exists (in which case, of course, cnt
should be 0!)

---

Clarification of Question by placain-ga on 20 May 2002 12:52 PDT

The table definitions are at the above-given source URL.

In addition to the outer join mentioned below (which I assume works; I
haven't used it), you could also use a LEFT JOIN, which has a bit more
understandable syntax:

SELECT    locations.loc_id, loc_name, count(pho_id) AS cnt 
FROM      locations
LEFT JOIN photos USING (loc_id)
GROUP BY  loc_name

This will return everything in locations, and join the photos data as
appropriate.

Good Day, : placain-ga 

Try an outer join like this:


WHERE    photos.loc_id = locations.loc_id(+) 

It should list all the locations regardless of photo data.

Best,
Brad-ga

the 'LEFT [outer] JOIN' syntax is 'better': it's part of the SQL92
standard. It makes your queries more readable too. The (+) is part of
the Oracle dialect, and as I have never tried MySQL, I would not know
if it works there. I have tried the 'LEFT JOIN' syntax in Postgresql,
Sybase, MSSQL, Access'97, and a bunch of others, where it performs
marvelously.