Google Answers Logo
View Question
Q: mysql joins ( Answered 5 out of 5 stars,   2 Comments )
Subject: mysql joins
Category: Computers > Programming
Asked by: placain-ga
List Price: $4.00
Posted: 20 May 2002 12:47 PDT
Expires: 27 May 2002 12:47 PDT
Question ID: 17105
In my photo album software ( ), 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.
Subject: Re: mysql joins
Answered By: alexander-ga on 20 May 2002 13:44 PDT
Rated:5 out of 5 stars
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
placain-ga rated this answer:5 out of 5 stars

Subject: Re: mysql joins
From: brad-ga on 20 May 2002 13:28 PDT
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.

Subject: Re: mysql joins
From: nikanj-ga on 21 May 2002 07:01 PDT
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

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy