PHP SQL LEFT JOIN multiple values

Question

I have been playing around a bit and tried to find a solution but getting a bit stuck.

I have a 'meetings' table with 2 columns holding an id from 'members' which I need the members details to show on my meeting list.

I tried this, but it does not work...

$q = "SELECT (meetings.id) AS meetings_id, meetings.columnA, meetings.ColumnB, (members.id) AS members_id, members.first, members.last FROM meetings **LEFT JOIN members ON meetings.columnA = members.id AND meetings.columnB = members.id**";
$r = mysqli_query($dbc, $q);
while($row = mysqli_fetch_assoc($r)) { }

I can only get it to work if I only join on a single value, either 'columnA' or columnB', but this obviously only retrieved the data of for a single member.

Though this way works but is not ideal...

$q = "SELECT * FROM meetings";
$r = mysqli_query($dbc, $q);
while($row = mysqli_fetch_assoc($r)) { 
    $qm = "SELECT * FROM members WHERE id = '$row[columnA]'";
    $rm = mysqli_query($dbc, $qm);
    while($rowm = mysqli_fetch_assoc($rm)) {
        if  ($row['columnA'] == $rowm['id']) { echo $rowm['first']; }
    }
    $qm = "SELECT * FROM members WHERE id = '$row[columnB]'";
    $rm = mysqli_query($dbc, $qm);
    while($rowm = mysqli_fetch_assoc($rm)) { 
        if  ($row['columnB'] == $rowm['id']) { echo $rowm['first']; }
    }
}

Is there a way to get the JOIN to work or better way, so that I just need to make a single query?

Thanks.

Try removing , (members.id) AS members_id, members.first, members.last from the SELECT section of your query. LEFT JOIN should already do this. Granted, in my experience, using a JOIN would add all of the columns of that table into the query results. I don't know how to filter the JOIN so that it doesn't select all of the columns. — Tiffany
– Tiffany, Commented Nov 1, 2018 at 13:26
@jarlh, Don't know why I didn't try that (brains gone fuzzy). It works though it outputs 2 rows, each with the respective value showing and missing on each rows i.e. row1 - only showing member based on ColumnA and row2- only member based on columnB — JasonF
– JasonF, Commented Nov 1, 2018 at 13:39
I have tried using 'GROUP BY meeting.id' but will only show the first row which is missing the ColumnB member data — JasonF
– JasonF, Commented Nov 1, 2018 at 13:39
@Tiffany, can't remove my columns from the query as then it won't know what to look for. Additionally this is the way to call only the columns you want to use i.e. opposite of filter out. — JasonF
– JasonF, Commented Nov 1, 2018 at 13:41

Gordon Linoff · Accepted Answer · 2018-11-01 14:00:35Z

1

I think you just want two left joins.

SELECT m.id AS meetings_id, m.columnA, m.ColumnB,
       me1.id AS members_id, me1.first, me1.last,
       me2.id AS members_id, me2.first, me2.last
FROM meetings m LEFT JOIN 
     members me1
     ON m.columnA = me1.id LEFT JOIN
     members me2
     ON me.columnB = me2.id;

answered Nov 1, 2018 at 14:00

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

Manoj Singh · Accepted Answer · 2018-11-01 14:03:09Z

1

Try this::

SELECT 
meetings.id AS meetings_id, 
meetings.columnA, 
meetings.ColumnB, 
members.id AS members_id, 
CONCAT(m1.first,' ',m1.last) as  columnA_Member
CONCAT(m2.first,' ',m2.last) as  columnB_Member
FROM 
    meetings 
INNER JOIN 
    members as m1 ON meetings.columnA = m1.id
INNER JOIN 
    members as m2 ON meetings.columnB = m2.id

edited Nov 1, 2018 at 14:03

answered Nov 1, 2018 at 13:34

Manoj Singh

2621 silver badge7 bronze badges

5 Comments

JasonF Over a year ago

Tx. It works though it outputs 2 rows, each with the respective value showing and missing on each rows i.e. row1 - only showing member based on ColumnA and row2- only member based on columnB. I have tried using 'GROUP BY meeting.id' but will only show the first row which is missing the ColumnB member data

Manoj Singh Over a year ago

is both ColumnA & ColumnB has distinct member id? OR one column can has a member id ?

JasonF Over a year ago

Both Column A and Column B has different members ids

JasonF Over a year ago

If I show only the values of columnA and ColumnB, they show as they should, but if I do this... if ($row["columnA"] == $row['members_id']) { echo $row["first"]; } and if ($row["columnB"] == $row['members_id']) { echo $row["first"]; }..... then this this then output separate rows

Manoj Singh Over a year ago

@JasonFI have updated my query, I think INNER JOIN best to use instead LEFT JOIN

Collectives™ on Stack Overflow

PHP SQL LEFT JOIN multiple values

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related