3129

Attempting left join with a 'where' that only applies if the join succeeds

Question:

I have articles, stored in an article table. Some articles have one or more photos, stored in a photo table with an article_id specified. Some photos are 'deactivated' (photo.active = '0') and should not be retrieved.

I'm trying to get articles for the home page, and one photo for each article with one or more photos. Like so:

SELECT article.id, article.date, article.title, photo.filename_small FROM (article) LEFT JOIN photo ON photo.article_id=article.id WHERE photo.active = '1' GROUP BY article.id ORDER BY article.date desc LIMIT 10

(The "group by" is so that I don't get multiple results for articles with multiple photos. It strikes me as awkward.)

When I have the WHERE photo.active = '1' like that, I only get results with a photo, which defeats the purpose of making the join a left join. The where is only relevant if the join matches the article with a photo, but it's ruling out all articles without active photos. Any ideas?

(Yes, there are similar questions, but I've read a lot of them and am still struggling.)

Answer1:

Two options.

<ol><li>

Put it in the join clause:

LEFT OUTER JOIN photo ON photo.article_id=article.id AND photo.active = 1 </li> <li>

Explicitly allow nulls again:

WHERE (photo.active = 1 OR photo.id IS NULL) </li> </ol>

The second seems unnecessarily complicated though as you already have the outer join. I'd recommend the first.

Answer2:

Try something like

SELECT article.id, article.date, article.title, photo.filename_small FROM (article) LEFT JOIN photo ON photo.article_id=article.id AND photo.active = '1' GROUP BY article.id ORDER BY article.date desc LIMIT 10

Recommend

  • Should one generate a unique id for each row in a database table that otherwise has no unique keys?
  • Linq BoilerPlate: Is all of it needed?
  • update statement not executing
  • Is it possible to return a value from setTimeout without a callback function?
  • Java :: obtaining parameterized type names at runtime
  • Read from an enum without pattern matching
  • DirectX11 Shader Compilation Issue
  • Referring to an object of a derived class from the base class of another (unrelated!) class in C++
  • How to save session with “save_with_current_level”?
  • Measure time taken dynamically linking at program startup?
  • How to update your homepage at a certain time?
  • AFNetworking synchronous calls (like/unlike)
  • Accounting for leap year in comparing year to year sales
  • Listbox within Listbox and scrolling trouble in Windows Phone 7 Silverlight
  • Disable Enter in editText android
  • Jetty Server not starting: Unable to establish loopback connection
  • Django: Count of Group Elements
  • MongoDB in PHP using aggregate to group by _id is null not working
  • Display issues when we change from one jquery mobile page to another in firefox
  • Illegal mix of collations for operation for date/time comparison
  • Read text file and split every line in MSBuild
  • Bug in WPF DataGrid
  • How to add a column to a Pandas dataframe made of arrays of the n-preceding values of another column
  • How to check if every primary key value is being referenced as foreign key in another table
  • Knitr HTML Loop - Some HTML output, some R output
  • How to handle AllServersUnavailable Exception
  • Can a Chrome extension content script make an jQuery AJAX request for an html file that is itself a
  • Release, debug version and Authorization Google?
  • To display the title for the current loaction in map in iphone
  • How to get next/previous record number?
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • Unit Testing MVC Web Application in Visual Studio and Problem with QTAgent
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • trying to dynamically update Highchart column chart but series undefined
  • python regex in pyparsing
  • Hits per day in Google Big Query
  • embed rChart in Markdown
  • need help with bizarre java.net.HttpURLConnection behavior
  • java string with new operator and a literal