Click here to Skip to main content
15,561,011 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello. Newbie here.
So ,
lets just say that
There is animals with ID, Name and a Foreign Key that is Species ID.
so that would be like
animalID animalname Species_specID(FK)

another one would be species ID and name
specID specname

another one would be diet Id and number of feeds per day
dietID numberoffeed

and the last one would be the table for diet and animal
diet_dietID(Fk) animal_animalID(Fk)

and the goal is to find how many of the specIDs is getting fed with number of feeds example (3 feeds perday)
but since there can be no relation to diet and species. how can i find that?

What I have tried:

i tried using joins but feels like , i am using it wrong

my code looks like this

select count(species.specID) as Total_species
From mydb.species
inner join animal on 
species.specID = animal.species_specID
inner join diet_feeds_animal on
animal_animalID = diet_dietID
inner join diet on 
diet.dietID = species.specID
where numberoffeed>3;

but this one keeps returning 0 as value. Can someone explain to me?
Updated 4-Dec-21 22:21pm

1 solution

Right: first off ignore the species table completely - it contains nothing relevant to your query - why would you need to retrieve names of species if you aren't using them?

Start with the "linking table": diet_feeds_animals and JOIN that to the two related tables.
Then GROUP BY the species ID to get the count.

Don't do it all at once: start by retrieving every column you can in all tables in the query so far to see what data you actually get from your query. When you are happy, move on to the next part. So start with a SELECT, and add one JOIN. Then add a second JOIN, then add the GROUP BY. Take it easy and don't rush into a "full solution" - it'll be more obvious as you move along if you can see exactly what your query is doing at each stage.
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900