CODEWITHSUNDEEP
mysqljoingroup-by
lStoilov
lStoilov

Reputation: 1339

grouping join query results

I am trying to write a query that shows me all orders with their options. So far that's done, but when I try to group them by order id it just print a separate row for each option and not the order ID and all its option under it.

I am missing something but can't figure out what is it :)

Here is what I have

$sql = 'SELECT *
        FROM `order` RIGHT JOIN `order_option`
        on order.order_id=order_option.order_id
        where order_status_id = 2 
        group by order_option.order_option_id';

$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
{
    print "ID:{$row['order_id']}  <br> ".
         "OPTION NAME: {$row['name']} <br> ".
         "VALUE: {$row['value']} <br> ".
         "--------------------------------<br>";
} 
echo "Fetched data successfully\n";
mysql_close($conn);

?>

**UPDATE 1**

This is how the query worked now

SELECT 
  o.order_id, 
  o.name, 
  o.value, 
  GROUP_CONCAT(o.order_id, o.name, o.value SEPARATOR ',') AS Options 
FROM `order_option` AS o 
LEFT JOIN `order` AS oo on o.order_id = oo.order_id 
where oo.order_status_id = 2 
group by o.order_id

Upvotes: 0

Views: 88

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79909

You should GROUP BY order_id instead, and use GROUP_CONCAT to get all the options of an order in the same row:

SELECT 
  o.order_id,
  GROUP_CONCAT(oo.order_option_id SEPARATOR ',') AS Options
FROM `order` AS o
RIGHT JOIN `order_option` AS oo on o.order_id = oo.order_id
where o.order_status_id = 2 
group by o.order_id;

SQL Fiddle Demo

If GROUP_CONCAT is not what are you looking for, then you have to use other aggregate functions, but don't include any columns that is not in the GROUP BY clause in the SELECT clause without an aggregate function. Otherwise you will got inconsistent data.

Update 1

The reason your query doesn't work:

SELECT 
  o.order_id, 
  o.name, 
  o.value, 
  GROUP_CONCAT(o.order_id SEPARATOR ',') AS Options 
FROM order_option AS o 
LEFT JOIN order AS oo on o.order_id = oo.order_id 
where oo.order_status_id = 2 
group by o.order_id

Becuase the two columns o.name, o.value in the SELECT clause are not neither in an aggregate function not in the GROUP BY clause, so you are getting inconsistent values of these tow columns, MySQL gets an arbitrary value for them. Try this instead:

SELECT 
  o.order_id, 
  o.name, 
  o.value, 
  GROUP_CONCAT(o.order_id SEPARATOR ',') AS Options 
FROM order_option AS o 
LEFT JOIN order AS oo on o.order_id = oo.order_id 
where oo.order_status_id = 2 
group by o.order_id, 
         o.name,
         o.value;

Upvotes: 1

Related Questions