quick Sql query help needed

[BootyBean]

Hi. I'm trying to create a query but I'm not sure how to get what I'm looking for.

Here is a sample structure to what I'm using with just 2 tables. It is a database to record bird observations.

[observation] //one row per date surveyed; one to many relationship to [birdtally]
ID, site, Date

[birdtally] //row for each bird seen on this observation date
ID, bird, Total

I want to return a list of dates and 'total' for a specific site and specific bird so that I can chart that bird's numbers for each survey. So I used this query:

SELECT DISTINCT birdtally.Total, DATE_FORMAT(observation.Date,'%m/%d/%Y') as 'humandate'FROM birdtally LEFT JOIN observation ON observation.ID = birdtally.ID WHERE site='Lake Boogaloo' AND Bird='Bald Eagle' ORDER BY observation.Date

This returns close to what I want. The problem is that it only returns dates on which a Bald Eagle was seen. I need for it to return a row for every date surveyed, with a '0' for dates which no Bald Eagles were seen.

So if my sample database looked like this:

[observation]
1, Lake Boogaloo, 2000/1/1
2, Lake Boogaloo, 2000/1/2
3, Lake Boogaloo, 2000/1/3

[birdtally]
ID, bird, Total
1, American Coot, 4
1, Bald Eagle, 2
1, Common Raven, 3
2, Mallard, 4
2, Ruddy Duck, 3
3, Bald Eagle, 3
3, American Coot, 5

Then I want the result to be:

1/1/2000, 2
1/2/2000, 0
1/3/2000, 3

[Julian Turner]

I am not sure if this helps, but you could try RIGHT JOIN instead.

I.e. you want to take each date, and then ascertain the numbers of bald eagles for that date, so the primary table for the select should perhaps be the observation table.

Alternatively, try:

observation LEFT JOIN birdtally

you should then get a NULL value in rows for any date where there was no bald eagle

[BootyBean]

I tried your suggestions and it didn't work. The root problem is the "Where Bird='Bald Eagle' " part of the query. This removes all of the dates surveyed that didn't have Bald Eagles, when I want it to keep the dates, with a null or zero.

Any other ideas?

[Kiwi]

JOIN defaults to an inner join; you need an OUTER JOIN

PHP Code:

SELECT O.Date, B.Total
FROM observation O
LEFT OUTER JOIN birdtally B
 ON B.id = O.id
  AND B.bird = "Bald Eagle"
WHERE O.site = "Lake Boogaloo"; 


I think this is correct - the outer join syntax is a bit of a mystery to me (I could write it for Oracle...). You may need to move the AND B.bird = "Bald Eagle" clause down to the where, but I was led to believe that conditions on the right hand table need to be in the ON clause.

You may have simplified it for the example, but if you haven't the fields 'site' and 'bird' should really be kept in seperate tables and referenced as a foreign key; the keys should be used as the conditions on this . If not, the results of this query will be very unreliable.

[BootyBean]

Thanks. That seems to be working fine. I appreciate the help.