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.
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; ?>
