CODEWITHSUNDEEP
phpmysql
user5000
user5000

Reputation: 129

Can't join 3 tables in mysql to link user ID

I am thinking maybe this isn't possible, but I want to join 3 tables at the ID (users) column, which all 3 tables have. Been at it for days now. This may be a bit confusing for anyone willing to help though...

The table architecture and table explanations are:

wp_users (ID, display_name) This is the users table

stories (ID, SID) This table stores the story name and who started it

writing (ID, SID, WID, text) This table stores the all the pieces of writing that different authors add to the SID, so there can be multiple rows of SID here, each with different user ID. ID is the user and matches the ID values in the other tables. WID is the pieces of writing for each SID.

Each story ID (SID) has multiple records in the writing table, each with a different writing ID. The stories table only has one SID and one ID--the person who started the story. My code is only giving the display_name based on the ID in this stories table (the person who started the story). I need it to show the display names for each piece of writing added by others for that SID, which are stored writing table.

The code currently works except that it gives the same author (display_name) for all pieces of writing stored in the writing table, even though there are different authors for each writing piece (WID).

The problem is, as soon as I try to add another join such as join wp_users on writing.ID = wp_users.ID I get no results for any variables. If I do not try to join this third table at 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 3 tables? Is it even possible to join 3 tables on ID? Would greatly appreciate any help...

Upvotes: 3

Views: 109

Answers (1)

Will Warren
Will Warren

Reputation: 1294

Sure it's possible to JOIN as many tables as you want. It just depends what results you're after. Also, I think you're after LEFT JOIN for these queries.

If your "main select" is going to be the writing table, then 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')

Upvotes: 2

Related Questions