mysql query not returning result if data is not in table

Question

Here is the query that almost works:

$query  = '';
$query .= ' SELECT highway_code.charge_id, 
        highway_code.act_abbr, 
        highway_code.short_form_wording, 
        highway_code.section, 
        highway_code.set_fine,  
        highway_code_arrest_jail_tow.demerits
        ';
$query .= ' FROM highway_code, highway_code_arrest_jail_tow ';
$query .= ' WHERE highway_code.charge_id = ? ';
$query .= ' AND highway_code.section = highway_code_arrest_jail_tow.section ';
$query .= ' AND highway_code.act_abbr = highway_code_arrest_jail_tow.act_abbr ';

The problem is that not all highway code charges have demerits, thus any charges without demerits are not returned, even though all the other fields apply. I would like to have all charges returned regardless if there is a value in highway_code_arrest_jail_tow.

You need a LEFT JOIN instead of the implicit INNER JOIN you have here (via comma-separated table names) Review codinghorror.com/blog/2007/10/… — Michael Berkowski
– Michael Berkowski, Commented Apr 17, 2013 at 13:20
Thank you for the link. I think that's the best explanation I've seen on the topic. However, as a novice with limited experience in creating SQL statements, I'm not sure how i'd write it out... I'm starting to do the research now, but if the solution is simple, would you mind posting? Tks! — Rob Stocki
– Rob Stocki, Commented Apr 17, 2013 at 13:30

Michael Berkowski · Accepted Answer · 2013-04-17 14:25:43Z

2

The use of comma-separated tables in the FROM clause as you have it, with an equality condition between the related columns implies an INNER JOIN. To enforce all records being returned from the primary table regardless of the presence of related records in the related table, use a LEFT JOIN instead.

I'll replace your implicit inner join with an explicit LEFT JOIN:

SELECT
  highway_code.charge_id, 
  highway_code.act_abbr, 
  highway_code.short_form_wording, 
  highway_code.section, 
  highway_code.set_fine,  
  highway_code_arrest_jail_tow.demerits
FROM
  highway_code
  LEFT JOIN highway_code_arrest_jail_tow
     /* Two relating conditions belong in the ON clause */
     ON highway_code.section = highway_code_arrest_jail_tow.section
     AND highway_code.act_abbr = highway_code_arrest_jail_tow.act_abbr
WHERE
  /* and the filtering condition remains in the WHERE */
  highway_code.charge_id = ?

answered Apr 17, 2013 at 14:25

Michael Berkowski

271k47 gold badges452 silver badges395 bronze badges

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

1 Comment

Rob Stocki Over a year ago

You sir, are amazing. I was so close, it was the WHERE at the end I was juggling with. Thank you so much Michael.

Collectives™ on Stack Overflow

mysql query not returning result if data is not in table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related