Reputation: 2032
I Have two tables trips_data which as tripid, userid, species (int),killcount masterspecies which had species_id and speceies (string)
I am trying to retrieve a list of all species seen on a trip I am hoping to get
sum(killcount) : tripid :species (string):species (int)
57 300 rabbit 1
2 300 foxes 2
1 300 squirels 8
and so on
i have the below query which returns everything I want except the sum(killcount) is about 8000 when it should be 57.
Any help would be hugely apreciated
SELECT sum(trips_data.killcount),
trips_data.species,trips_data.spceces,
masterspecies.species
from trips_data
join masterspecies
WHERE tripid=$tripid
AND userid=1
AND NOT killcount=0
Upvotes: 1
Views: 79
Reputation: 2870
This is a cartesian join:
from trips_data join masterspecies
This will return a record for every combination of records from the two tables. That is usually not the intention. Join conditions look something like this:
from trips_data
join masterspecies
on masterspecies.species_id = trips_data.species_id
This will match the records up and only return matching records, so there is a chance your sum will come out correctly.
Upvotes: 1
Reputation: 5009
You need to tell the database how to join; otherwise you're getting every possible combination. It looks like trips_data.species should match master_species.species_id; is that right? You also need to group the results by species.
SELECT sum(trips_data.killcount), trips_data.species, masterspecies.species
from trips_data join masterspecies
WHERE tripid=$tripid AND userid=1 and trips_data.species=masterspecies.species_id
group by trips_data.species, masterspecies.species;
Upvotes: 2