By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,136 Members | 1,089 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,136 IT Pros & Developers. It's quick & easy.

Help needed with a complex query

P: n/a
Hi. I'm not a SQL power user but can handle most simple queries. But I have
no idea how to approach this problem...hopefully someone can point me in the
right direction.

I have a table which lists point numbers, bird species that was seen at each
point, and the number of birds seen. So, if I saw 5 different kinds of birds
at point #1, then there are 5 rows with '1' in the Point column and the bird
name in the 'Species' column, and a 3rd column listing the number of that
bird seen.

I then do a select query, using a WHERE statement to filter for the bird I
want to view. The end result of the query is a table that lists points, bird
(that match the where statement), and number of birds. No problem. This
works fine.

Here is the part I don't know how to handle. If I have 10 points (1-10) but
the birds I'm searching for (via the WHERE) are only seen on points 3 and 4,
I only get points '3' and '4' in the result. The number of points and how
they are labeled vary (ie. some are 1,2,2a,3,3a... and others are
1,2,3,4...). So, in the case that there is a match only on points '3' and
'4', I want the resulting table to list ALL of the points with '0' as the
number of birds.

In short, I want to return a row with a '0' in the number of birds column
for rows that do not match the WHERE statement. And I only want 1 of there
rows per point...not 1 for every bird that doesn't match the WHERE clause.

Hopefully that makes sense. Let me know if it doesn't.


Nov 12 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.