How can I write a query for tables that do not have relation to each other?

Question

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

animal
animalID animalname Species_specID(FK)

another one would be species ID and name

species
specID specname

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

diet
dietID numberoffeed

and the last one would be the table for diet and animal

diet_feeds_animals
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?

Posted 4-Dec-21 21:03pm

Bonzowo

Updated 4-Dec-21 21:21pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Accepted Answer · 2021-12-04T21:21:00

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.