mySQL select with PHP - left join

Question

I have two tables 'all' and 'jdetails'. I have an existing select query on the all table which works. I want to add some additional data from jdetails table if available.

all table:

judge, year, ...

jane doe, 2012

john doe, 2011

jdetails table:

name, designation,...

jane doe, level 1

jane doe, level 5

john doe, special

How do I change my query below to include the 'designation's (from jdetails) for each judge (in all)?

I think a left join is the solution but I have the where clause to consider. Also, I absolutely must have the results of this query below, but with added data from jdetails table if it exists.

Additionally, there can be multiple rows (jdetails.name) of designations for each all.judge which I want listed as a single value. e.g.-- jane doe would have designation value of 'level 1 level 5'.

I would join on all.judge=jdetails.name

current query:

$rows = $my->get_row("SELECT all.judge, `year`, `totlevel_avg`, `totlevel_count`,   `genrank`, `poprank`, `tlevel_avg`, `tlevel_count`, `1level_avg` as `onelevel_avg`, `1level_count` as `onelevel_count`, `2level_avg` as `twolevel_avg`, `2level_count` as `twolevel_count`, `3level_avg` as `threelevel_avg`, `3level_count` as `threelevel_count`, `4level_avg` as `fourlevel_avg`, `4level_count` as `fourlevel_count`, `PSGlevel_avg`, `PSGlevel_count`, `I1level_avg`, `I1level_count`, `I2level_avg`, `I2level_count`, `GPlevel_avg`, `GPlevel_count`, `states` from `all` where `id` ='{$term}'");

any help is greatly appreciated.

Linger · Accepted Answer · 2014-02-12 21:00:02Z

1

I did not include everything you had in your SELECT statement, I just summarized it with ams.*. But the following links the all table to the jdetails table and then groups the designation into one field. Then I wrap the results in an outer query that pulls the rest of the fields you need in the all table (SQL Fiddle):

SELECT ams.*, am.Desigs
FROM 
(
  SELECT a.judge, GROUP_CONCAT(j.designation SEPARATOR ', ') AS Desigs
  FROM `all` AS a
  INNER JOIN jdetails AS j ON a.judge = j.name
  GROUP BY a.judge
) AS am 
INNER JOIN `all` AS ams ON am.judge = ams.judge

edited Feb 12, 2014 at 21:00

answered Feb 12, 2014 at 20:33

Linger

15.1k23 gold badges54 silver badges79 bronze badges

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

Comments

Jag · Accepted Answer · 2014-02-12 20:41:55Z

0

Here is an example that can help you:

`SELECT table1.column_name(s),table2.column_name(s) FROM table1 
LEFT OUTER JOIN table2 ON table1.column_name=table2.column_name
 AND table1.column_name='Parameter'`

Where table 1 is all and table 2 is jdetails

answered Feb 12, 2014 at 20:41

Jag

2911 silver badge4 bronze badges

Collectives™ on Stack Overflow

mySQL select with PHP - left join

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related