0

I'm looking to join three tables under a condition. Table1 is the "main" table that has the related id's to the other two tables.

I know how to make a single join:

SELECT name, column2
FROM table1
LEFT OUTER JOIN table2 ON table1.id2 = table2.id2

However I am unable to connect it with a second JOIN to obtain column3 using table1.id3 = table3.id3.

There is an additional difficulty as I only want to display those records where name = $name (a predefined variable).

table1

id | id2 | id3 | name

table2

id2 | column2

table3

id3 | column3

Any tips on how to connect the three statements are greatly appreciated.

Edit:

SELECT name, column2, column3 FROM table1
LEFT OUTER JOIN table2 ON table1.id2 = table2.id2
LEFT OUTER JOIN table3 ON table1.id3 = table3.id3

I've managed to join the three tables and my only issue is adding the name=$name condition.

I've tried 

SELECT name, column2, column3
FROM table1
WHERE name=$name
LEFT OUTER JOIN table2 ON table1.id2 = table2.id2
LEFT OUTER JOIN table3 ON table1.id3 = table3.id3

and

SELECT name, column2, column3
FROM table1
LEFT OUTER JOIN table2 ON table1.id2 = table2.id2
LEFT OUTER JOIN table3 ON table1.id3 = table3.id3
WHERE name=$name

so far.

Paul Spiegel
  • 30,925
  • 5
  • 44
  • 53
Laura
  • 83
  • 7
  • 1
    What is the problem you are having? What have you tried? – Uueerdo May 25 '18 at 20:07
  • @Uueerdo I'm unable to add the second JOIN as well as the `name=$name` to the first JOIN statement (posted in the question). I tried adding another OUTER LEFT JOIN at the end of the first statement though that doesn't work. – Laura May 25 '18 at 20:13
  • 2
    It's customary/expected to show what you've tried. This helps in pinpointing the mistake, and prevents questions from looking like "do this for me". – Uueerdo May 25 '18 at 20:14
  • `OUTER LEFT JOIN` works? I thought it should be `LEFT OUTER JOIN`. – Paul Spiegel May 25 '18 at 20:14
  • `OUTER` is optional by the way... _and in my opinion redundant_. – Uueerdo May 25 '18 at 20:15
  • @Uueerdo I've updated the question with what works & what I tried. – Laura May 25 '18 at 20:24
  • @PaulSpiegel I was indeed LEFT OUTER JOIN, sorry. – Laura May 25 '18 at 20:24
  • So what's wrong with the last query? – Paul Spiegel May 25 '18 at 20:27
  • @Laura that last one looks right (WHERE comes after FROM and any JOINs); how are you trying to execute it? – Uueerdo May 25 '18 at 20:27
  • 2
    $name indicates a php variable, but all we have seen so far is sql code. Where is the php code? Also, the name field indicates a string data type, so probably needs enclosing single quotes. – Shadow May 25 '18 at 20:28
  • Thank you @Shadow for pointing me in the right direction. What I was trying was `$q = "[...] WHERE name =" . $name ;` but what actually worked was `$q = "[...] WHERE name = '" . $name . "'";` . Thanks everyone for helping. – Laura May 25 '18 at 20:38
  • 2
    You should use a prepared query instead of substituting variables. That will solve the quoting problem and also prevent SQL-injection. – Barmar May 25 '18 at 21:11

0 Answers0