MYSQL Join with Null value returned

Question

I have a query thats joining two table, using the GROUP_CONCAT to get a comma separated list which is then being mapped to an array in an object

SQL:

    $sql = "SELECT *,
            GROUP_CONCAT(climb_attributes.attribute_id) as climb_attributes
            FROM climbs
            LEFT JOIN climb_attributes ON
            (climbs.id = climb_attributes.climb_id)
            GROUP BY climb_id
            ORDER BY climbs.id";

PHP

$all_climb_profiles[$climb->id]->attributes = explode(",", $climb->climb_attributes);

Nearly working perfectly, except I currently only get back results IF the climb_attributes table contains the climb id. Essentially a climb can still exist even if it doesn't have any attributes, but at the moment it has to have an attribute to be returned in the results.

I also need to join it to another table to get the attribute name for the attribute id...if you can help with that as well that would be great, I'm hoping I can figure that out though.

Gordon Linoff · Accepted Answer · 2015-04-01 17:58:42Z

1

First, you should not be using * to select from all tables when using group by. You can safely take all the columns from the climb table.

The problem is that you are aggregating on a column in the second table, rather than the first. And, it is NULL if there is no match. So, a better query is:

SELECT c.*, GROUP_CONCAT(ca.attribute_id) as climb_attributes
FROM climbs c LEFT JOIN
     climb_attributes ca
     ON c.id = ca.climb_id
GROUP BY c.id
ORDER BY c.id;

EDIT:

If you want to list the strings, then something like this should work:

SELECT c.*, GROUP_CONCAT(a.name) as climb_attributes
FROM climbs c LEFT JOIN
     climb_attributes ca
     ON c.id = ca.climb_id LEFT JOIN
     attributes a
     ON ca.attribute_id = c.id
GROUP BY c.id
ORDER BY c.id

edited Apr 1, 2015 at 17:58

answered Mar 31, 2015 at 16:15

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

Kyle Goslan Over a year ago

Perfect, thank you, and for the explanation as well. Can you help me link the ca.attribute_id to a third table containing the name for the attribute_id, its just has id and attribute_name column

Kyle Goslan Over a year ago

I've been trying to figure work out how I can turn the ca.attribute_id into the actual value of the attribute_id, which is stored in a third table which just contains id and attribute columns, so rather than just a list of numbers, I get the actual value associated with the number.

Collectives™ on Stack Overflow

MYSQL Join with Null value returned

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related