How to join values from a nested table value from mysql database

Question

Hi folks I'm learning how to code (php and mysql) by following online tutorials and am building my first website from scratch.

I am stumped with a problem and I don't know the best search keywords to get results in google. I hope someone can help.

I am familiar with the

SELECT ... FROM ... JOIN ... ON ...

and it works really nicely when working with directly related tables but when a table is ... 'distant related' (I don't know how to phrase this) then I get an error in mysql.

Please see the image below. https://i.sstatic.net/mfNXT.png I have 5 tables.

I want to output a html table using code similar to

"SELECT horseRaces.ID, horseRaces.stall, horseRaces.draw, horses.name, horses.birth, horseRaces.win, horseRaces.place, horseStatus.name
FROM horseRaces
JOIN horses ON horseRaces.horseID = horses.ID
JOIN horseStatus ON horseRaces.horseStatusID = horseStatus.ID
ORDER BY stall"

Hope someone can help me because I'm stumped. All my google searches brings inner join and outter join results.

Why don't you simply use inner join and let the SQL engine take care of the performace? ---- "SELECT horseRaces.ID, horseRaces.stall, horseRaces.draw, horses.name, horses.birth, horseRaces.win, horseRaces.place, horseStatus.name FROM horseRaces, horses where horseRaces.horseID = horses.ID and horseRaces.horseStatusID = horseStatus.ID ORDER BY horseRaces.stall" — Akshaya Shanbhogue
– Akshaya Shanbhogue, Commented Nov 7, 2013 at 11:53
Distant related? Do you mean tables that are not directly related; i.e., tables that are related through other tables? If so, you ned to build a "chain of joins"- just keep joining tables with additional JOINs until you get all the tables you want. — AgRizzo
– AgRizzo, Commented Nov 7, 2013 at 11:56
WOW thanks for all your replies! I'm reading through all the recommendations now and testing them. "chain of joins" sounds like what I'm after. Hmm OK brb. Back to the drawing board. — Peter Reginald
– Peter Reginald, Commented Nov 7, 2013 at 12:35

OGHaza · Accepted Answer · 2013-11-07 12:19:42Z

0

Sticking to your writing style:

SELECT 
    horseRaces.ID
    ,horse.Name
    ,country.Name
    ,horseGender.Name
    ,jockey.Name
FROM horseRaces
JOIN horses ON horseRaces.HorseID = horses.ID
JOIN jockey ON horseRaces.JockeyID = jockey.ID
JOIN country ON horses.CountryID = country.ID
JOIN horseGender ON horses.GenderID = horseGender.ID
ORDER BY horseRace.ID, horse.ID

Should produce the table you are looking for.

I recommend giving aliases to each table so you don't have to write out the table name each time, also in this case if the horseID, jockeyId, countryID and genderID cannot be null then the the corresponding join should be INNER (don't worry about that too much though)

SELECT 
    hr.ID
    ,h.Name AS HorseName
    ,c.Name AS Country
    ,hg.Name AS HorseGender
    ,j.Name AS JockeyName
FROM horseRaces hr
INNER JOIN horses h ON hr.HorseID = h.ID
INNER JOIN jockey j ON hr.JockeyID = j.ID
INNER JOIN country c ON h.CountryID = c.ID
INNER JOIN horseGender hg ON h.GenderID = hg.ID
ORDER BY hr.ID, h.ID

edited Nov 7, 2013 at 12:19

answered Nov 7, 2013 at 12:01

OGHaza

4,7957 gold badges26 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Peter Reginald Over a year ago

Thanks for replying OGHaza. I will read up on creating aliases for my tables. I've been using JOIN instead of INNER JOIN thinking they did the same thing... I'll test out it out now.

OGHaza Over a year ago

No worries, I'd stick with just using JOIN for now anyway to keep things simple. If this answer was what you needed please hit the tick button next to it to accept so I can build up my rep

Peter Reginald Over a year ago

I want to thank you again @OGHaza. You really helped me. You helped me to understand the syntax of JOIN/INNER JOIN. I'll read up on aliases for tables as I had not heard of such a thing before you mentioned it. I hope you have an awesome day!

OGHaza Over a year ago

Haha thanks no problem. There will be plenty of tutorials on aliases, they are very simple. Its just FROM table AS t Or just FROM table t and then you can reference Table.Column as t.Column rather than writing the table name repeatedly

Joke_Sense10 · Accepted Answer · 2013-11-07 12:05:19Z

0

Try this:

SELECT hr.ID, h.name, c.name, cg.name, j.name FROM horseRace hr 
LEFT JOIN horses h ON h.ID = hr.horseID 
LEFT JOIN country c ON h.countryID = c.ID 
LEFT JOIN horseGender hg ON hg.ID = h.genderID 
LEFT JOIN  jockey j ON hr.jockeyID = j.ID 
order by h.ID

answered Nov 7, 2013 at 12:05

Joke_Sense10

5,3972 gold badges20 silver badges22 bronze badges

Comments

Sergiu Costas · Accepted Answer · 2013-11-07 12:53:32Z

try this:

$sql="Select 
    hr.ID as ID,
    h.name as HNAME,
    c.name as COUNTRY,
    hg.name as HGENDER,
    j.name as JOKEY
from horseRase hr
left join hourses h
on hr.horseID = h.ID
left join country c
on h.countryID = c.ID
left join hourseGender hg
on h.genderID = hg.ID
left join jokey j
on hr.JokeyID=j.ID

ORDER BY hr.ID
";
$result = mysql_query($sql) or die();

echo "<table>
    <tr>
        <td>ID</td>
        <td>HORSE NAME</td>
        <td>COUNTRY</td>
        <td>GENDER</td>
        <td>JOKEY</td>
    </tr>
";

while($row = mysql_fetch_array($result)){
$ID=$row['ID']; 
$HORSE=$row['HNAME']; 
$COUNTRY=$row['COUNTRY']; 
$HGENDER=$row['HGENDER']; 
$JOKEY=$row['JOKEY']; 

echo "
    <tr>
        <td>$ID</td>
        <td>$HORSE</td>
        <td>$COUNTRY</td>
        <td>$HGENDER</td>
        <td>$JOKEY</td>
    </tr>
";

}
echo "</table>"

Collectives™ on Stack Overflow

How to join values from a nested table value from mysql database

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related