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

 

[oneilldo]

SELECT * FROM Sightings,Birds
WHERE sightings.BirdID = Birds.BirdID
AND NOT EXISTS (SELECT 1 FROM Sightings s2,Birds b2 WHERE s2.BirdID = b2.BirdID AND s2.BirdId = sightings.BirdId AND s2.UserID = 47)
GROUP BY sightings.BirdID

should be about right

 

[Haircut]

SELECT * FROM Sightings,Birds
WHERE sightings.BirdID = Birds.BirdID
AND NOT EXISTS (SELECT 1 FROM Sightings s2,Birds b2 WHERE s2.BirdID = b2.BirdID AND s2.BirdId = sightings.BirdId AND s2.UserID = 47)
GROUP BY sightings.BirdID

should be about right

This will still suffer from the same problem, if user 47 and a user other than 47 both see bird ID 1 this query will still bring back bird ID 1.

This should work

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
    );

 

[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

 

[ByteJuggler]

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

Maybe something like this (using a sub query):

select 
  b.*, summ.SightingCnt 
from
  ( select birdID, count(*) SightingCnt 
    from sightings s 
    where s.userID <> 47 
    group by s.birdID
  ) summ
left join
  birds b on summ.birdID = b.birdID
where 
  summ.birdID not in (select distinct (birdID) from sightings where userID = 47)

The sub-query basically produces a list of birdid's and the number of times each was sighted, apart from sightings by userID 47. The outer query then uses this set and joins on the rest of the bird information and excludes from the summary birds that have been sighted by userID 47 as per your requirement.

 

[SoapSurgeon]

thanks, that was exactly what i needed

 

[Scarylion]

not wanting to fill the board with MySQL questions i thought i'd post my quickie in here

I need to select 2 different values from the same column but i cant figure it out :s

can get it to select one value just fine using

$sql = mysql_query("SELECT * FROM tblsectionresult WHERE site_name = 'Test'");

but i need it to select the results like Test & Test 2 from the site_name, if you get my meaning?

thanks

-Ben

 

[ByteJuggler]

not wanting to fill the board with MySQL questions i thought i'd post my quickie in here

I need to select 2 different values from the same column but i cant figure it out :s

can get it to select one value just fine using

$sql = mysql_query("SELECT * FROM tblsectionresult WHERE site_name = 'Test'");

but i need it to select the results like Test & Test 2 from the site_name, if you get my meaning?

thanks

-Ben

Try:

$sql = mysql_query("SELECT * FROM tblsectionresult WHERE site_name LIKE 'Test%'");

or

$sql = mysql_query("SELECT * FROM tblsectionresult WHERE LEFT(site_name, 4) = 'Test'");

 

[Scarylion]

sorted it thanks