How to get name from another table by id

Hi all,

I’ not sure how to get this done, so I ask your help.

I have two tables, groups and task.

gid         gname
-----------------------------
79           Foxes
102          Rabbits
51           Turtles
172          Robins



tid         task_title                         first_group_id        second_group_id
-----------------------------------------------------------------------------------
1           Something                          79                     102
2           Something else ect                 51                     172

As you can see in the task table I store the groups’s ID.
How should I join the two table to get the group names instead of the ID-s?

I want something like this on the output when I select from ‘task’:

(1) Something: Foxes vs Rabbits

What you want to do is JOIN your query. Specifically, you want to do it twice (once for first_group_id and once for second_group_id).

https://www.w3schools.com/sql/sql_join.asp

ugh, i really dislike how simple w3schools is

also, Say NO to Venn Diagrams When Explaining JOINs

finally, i think it’s really important in this particular scenario of two joins to the same table that you at least mention table aliases, without which OP is going to be lost

Have to say I dont like their attempted visualization of a cross product. I understand it, but if I were new to databases and you showed me that, I’d go crosseyed, rather than find the cross product.

Not to mention that they’re really going to lecture people about what visualization to use when they’re throwing SELECT *'s around like its a party?

I’m trying to remember the last time in a join scenario I actually wanted the cross join and not the inner join - which is actually a Venn Diagram when there exists a 1?-1? relationship between the tables.

Definitely should have mentioned aliases though, thats a good point.

ooookay, I know I have to join but how do I do it twice and how can I output the result?
The code below of course makes no sense since I only get the name of the first group twice.

$stmt = $pdo->prepare('SELECT task.tid, task.task_title, task.first_group_id, task.second_group_id,  groups.gid, groups.gname FROM task INNER JOIN groups ON task.first_group_id = groups.gid ORDER BY task.tid DESC');
$stmt->execute();
$a_loop = $stmt->fetchAll(PDO::FETCH_ASSOC);
<?php foreach ($a_loop as $result): ?>

<?=$result["task_title"];?>: <?=$result["gname"];?> vs <?=$result["gname"];?>
<hr>

<?php endforeach; ?>

so two things;
1, as Rudy pointed out, we’re going to need a table alias, because we’re joining the same table to this query twice.

If I said “Take the groups that match the first_group_id, and the groups that match the second_group_id, and give me a singular group name”, you’d be confused if you were a computer - which group name did i mean? The one that matched the first id, or the second?

So we change things slightly:
“Take the groups that match the first_group_id and call that firstgroup, and the groups that match the second_group_id and call that secondgroup.”
INNER JOIN groups AS firstgroup ON task.first_group_id = firstgroup.gid
Then, the select reference will need to change to reflect the new alias: groups.gid, groups.gname would change to firstgroup.gid, firstgroup.gname.

  1. To do it twice… well, do it twice. You can chain JOINs together as much as needed;
    x INNER JOIN y INNER JOIN z. Note that the SQL engine will parse these in order, so it will do the inner join to y first, and then inner join the resulting mixed table with z - this can be important, depending on the join types and order used. In this situation, it won’t make a difference.

Ahha, I think I’m getting there but something is still wrong.

$stmt = $pdo->prepare('SELECT task.tid, task.task_title, first_group.gid, first_group.game, second_group.gid, second_group.game FROM task INNER JOIN groups AS first_group ON task.first_group_id = first_group.gid INNER JOIN groups AS second_group ON task.second_group_id = second_group.gid ORDER BY task.tid DESC');

Now it outputs the second group names twice.

<?php foreach ($a_loop as $result): ?>

<?=$result["task_title"];?>: <?=$result["gname"];?> vs <?=$result["gname"];?>
<hr>

<?php endforeach; ?>

So the general implication is - as you aliased the table names, alias the selection as well. -
SELECT ... first_group.gname AS fgname

<?=$result["fgname"];?>

1 Like

I see! Okay, thanks for your help, it works now. :wink: