Can't join 3 tables in mysql to bind user id
I think it might not be possible, but I want to join 3 tables in the ID (users) column that all 3 tables have. Been with him for several days. This can be a little confusing for those looking to help, though ...
Table architecture and table explanations:
wp_users (ID, display_name) This is the users table
Stories (ID, SID) This table stores the name of the story and who started it
writing (ID, SID, WID, text) This table stores all the fragments of the letter that different authors add to the SID, so there can be several SID lines, each with a different user ID. ID is the user and corresponds to the ID values in other tables. WIDs are the pieces of the record for each SID.
Each history ID (SID) has multiple entries in the record table, each with a different record ID. There is only one SID in the story table and one ID - the person who started the story. My code only gives the display_name based on the id in this story table (the person who started the story). I need to display display names for each piece of email added by others for that SID, which are stored in a writing table.
The code currently works, except that it gives the same author (display_name) for all the record chunks stored in the written table, although there are different authors for each record chunk (WID).
The problem is that as soon as I try to add another join like join wp_users on write. ID = wp_users.ID I am not getting any results for any of the variables. If I don't try to join this third table in ID, the code works, but it will only display
SELECT wp_users.ID, wp_users.display_name,
stories.ID, stories.SID, stories.story_name, stories.category,
writing.ID, writing.text, writing.approved
FROM stories
JOIN wp_users ON stories.ID = wp_users.ID
JOIN writing ON stories.SID = writing.SID
WHERE (stories.SID = $the_SID) AND (writing.approved = 'Y')
");
<?php
foreach ($results as $result) {
echo "<br>" . "Author: " . $result->display_name . "<br>" . $result->text . "<br>";
Could part of the problem be that the name id cannot be the same for all three tables? Is it even possible to join 3 tables by ID? Any help would be greatly appreciated ...
source to share
I am sure it is possible <<20> as many tables as you like. It just depends on what results you are after. Also, I think you are following LEFT JOIN
for these requests.
If your "main pick" is going to be a table writing
, try something like:
SELECT
wp_users.ID,
wp_users.display_name,
stories.ID,
stories.SID,
stories.story_name,
stories.category,
writing.ID,
writing.text,
writing.approved
FROM writing
LEFT JOIN stories on writing.SID = stories.SID
LEFT JOIN wp_users ON writing.ID = wp_users.ID
WHERE (stories.SID = $the_SID) AND (writing.approved = 'Y')
source to share