MySQL Limiting Left Joins

Posted by on December 6, 2011 in Database, MySQL | 0 comments

Sometimes it’s nice to just use a left join to see if a column exists. That’s what left joins are for. Your base query will still return rows even if there are no rows in the joined table(s). However, what if there is a one to many relationship between the base table and the joined table, that is to say that for any row in the base table there could be any number of rows in the joined table. If you have more than one row in the joined table, it will return extra rows in your result, just duplicating the data in the base table.

If you need to have only 1 row returned, you’ll need to group by an id on the base table that will collapse all the extra rows. A primary key would work. Now if you need to limit it to 1 row however, there are some stipulations. The first is that you need to be able to use a groupby in the query. Some situations would be impossible to use a groupby. The second is that if you need the distinct information from the left join, then you’ll need to do group concats or similar to get the data. Otherwise you’ll only get the data from 1 of the rows in the left join. This is fine if you can work with the stipulations. If you’re more worried about whether or not a record exists in the joined table, this method will work fine, as long as you check a column that will always have a result, like the primary key of the joined table.

If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.

Leave a Comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>