SELECT LEFT JOIN (and return array of records from right table)

[johnsmith153]

SELECT field, (array of values from table 2) FROM $table1 t1 LEFT JOIN $table2 ON t1.id=t2.id

... is something like this possible? (where I return records from t1 in a LEFT JOIN, but also get an array of any of the fields/records from the table 2)

[r937]

a database query does not produce arrays

a database query produces a result set, which is exactly like a flat file -- it has rows and columns

other than that, yes, what you are asking is possible

[johnsmith153]

Thanks. Can you help then?

[johnsmith153]

Something like...

SELECT field (SELECT field1, field2 FROM $table2) FROM $table1 LEFT JOIN $table2 ....

???

[r937]

you're new to joins? see this article: The FROM Clause

SELECT t1.field 
     , t2.field1
     , t2.field2 
  FROM $table1 AS t1
LEFT OUTER
  JOIN $table2 AS t2
    ON t2.foo = t1.bar

[johnsmith153]

Thanks, but I'm pretty certain your query won't return a load of values from table 2.

Table 1:
name / id
Dave / 1
Steve / 2

Table 2:
id / amount
1 / 10
1 / 15

I need to be able to return

Dave (10, 15)

I definitely don't need a right join (which I know you've not suggested). I need a left join, but to still get all the values from the right table (as shown just above).

[r937]

Dave (10, 15)

so, a comma-delimited list?

mysql can do that

SELECT t1.name
     , GROUP_CONCAT(t2.amount) AS amounts
  FROM table1 AS t1
LEFT OUTER
  JOIN table2 AS t2
    ON t2.id = t1.id
GROUP
    BY t1.name

[johnsmith153]

Perfect, thanks a lot!!