Join, Multi query, something else?

Beginners - Learning PHP
#1

I’m at a complete loss at the moment. It feels like things are starting to come together in my head, but it’s like they’re still so far apart that I can’t just connect everything on how to make this work.
I have three tables. I don’t need to use them all, but this is how I set them up.

horse - stores basic information for horses to build pages from
pedigree - has the horses’ pedigrees (ancestors) in here
horses - stores basic information and pedigree information

I can use my horses table to display the information on each horse that I need, along with their pedigree. What I cannot figure out how to do is to tell the database/php/what not to search each horse from the pedigree for more info.

Example being the sire. I want it to not only find the sire and bring up his name, I want it to search the table again to see if that horse’s name is in my table. If it is, I want it to display certain things on the page. Then do the process again for the siresire. (Sire and siresire are both rows in my horses table)

Or I can ask it to search the pedigree table for the sire/siresire, then ask it to search the horse table for the sire’s name, then pull up the information.

I’ve tried multi query, joins, etc. and I’m just not grasping how to make this work. If someone could please just point me in the right direction of how to make this happen - I’ve no problems studying and figuring it out, but I’ve been hitting a brick wall on this issue for months now.

#2

Sounds like you want an inner join with a self joining query.

What is the structure?

#3

I definitely feel like a beginner lol. The horses table (that I would like to use in order to get rid of pedigree and horse table)

is something like
id - name - url - age - color - breed - sex - height - sire - siresire - siresiresire - sirex4 - siresiresiredam - and a lot more for the rest of a four generation pedigree.

horse table is basically
id - name - url - age - color - breed - sex - height

pedigree is
id - name - sire - siresire - siresiresire - sirex4 - siresiresiredam - siresiredam - siresiredamsire - etc for four generation pedigree

#4

This seems to be moving towards a worse db design (repeating columns sire, siresire etc. and repeating data each horse needs its own three of origins)

I’d leave the horse table, just throw in a sire_id and dam_id or similar. This allows you to find both the parents and child of each node through queries. The next step would be to look into building a three based on the parent/child nodes from wherever you start in the chain

2 Likes
#5

I have too much on my mind to think thru the data, but I have done this before on a project for Dog Shows. The queries do get complex however.

#6

I’m googling and not understanding how or if a tree would help me as from what I’m reading it says that a tree can only have one parent.

I do have a way to search for the offspring of a horse, and am wondering if I can somehow reverse this. Actually in the process of trying this now. Fingers crossed that it works.

#7

It would be something like this:

http://sqlfiddle.com/#!9/1f860f/27

It is a simplification in some ways, you should also have a breed table and a few other things that like to the horse, rather than having a varchar field to hold the values. But it gives you an idea of how the table scheme would work.

Sponsor our Newsletter | Privacy Policy | Terms of Service