MySQL Query confusion

[SoapSurgeon]

Hi,

I have a database that stores information about bird watchers, birds and the birds that each bird watcher has seen. Im trying to write a query that returns all the birds that a particular bird watcher hasnt seen, but has been seen at least once by another bird watcher. Currently i have:

SELECT * FROM Sightings,Birds WHERE sightings.BirdID = Birds.BirdID AND Sightings.UserID <> 47 GROUP BY sightings.BirdID

I thought this would be enough but it returns birds that bird watcher 47 has seen. So in affect its returning all birds that have been seen by someone else where i need it to exclude the whole 'group' not the individual sightings.

I hope this makes sense!

Matt

 

[SoapSurgeon]

SELECT *
FROM birds   b
WHERE NOT EXISTS
     (SELECT 1
      FROM sightings  s
      WHERE s.userID = 47
      AND   s.birdID = b.birdID
     )
AND EXISTS
    (SELECT 1
     FROM sightings s2
     WHERE s2.birdID = b.birdID
    );

Thanks that works a treat.... although I wanted to have a count of how many other people had seen that bird as well. Ive tried putting count(birdID) in but that throws an error. Any pointers... also id be interested in an explanation as to what that query is doing, if you can be bothered.

Thanks,

Matt

 

[SoapSurgeon]

thanks, that was exactly what i needed