join query and grouping to show result

Question

I have the following query which returns 9000 records:

SELECT Table1.id
, Table1.value 
, Table2.client_id 
, Table1.id_key
, Table3.id
, Table3.title
FROM Table1
LEFT JOIN Table2 ON Table2.id = Table1.Table2_id
LEFT JOIN Table3 ON Table1.id_key = Table3.id
WHERE Table2.orderType = "Grace"
AND Table2.id IN (7364)

From that I need to create a report that will have heading title of Table1.value and then it will show all the Table3.id, Table3.title

I have tried:

SELECT Table1.id  
    , Table1.value 
    , Table2.client_id 
    , Table1.id_key 
    , Table3.id 
    , Table3.title
    FROM 
    Table1
    LEFT JOIN Table2 ON Table2.id = Table1.Table2_id
    LEFT JOIN Table3 ON Table1.id_key = Table3.id
    WHERE Table2.orderType = "Grace"
    AND Table2.id IN (7364)
    GROUP BY Table1.value

But that reduces the results to 74 which is ok but I cant see anything else under those heading..what can I do.

What is it you're trying to see? Can you provide some more info? Maybe a sample output you're expecting? I *think* you want some aggregating functions on the other columns you're selecting, like MAX() or SUM(). — Dan, May 25 '11 at 00:33

score 2 · Accepted Answer · answered May 25 '11 at 00:46

Sorry for putting this in an answer, but as of yet I have no enough rep to comment.

what i understand is you are trying to get values of table 3 (several rows) in relation to a row in table 1. how about:

Your same first query without the group by and add order by table1.value ASC,[another order here] , then in your code only display the header if it is new, something like this

   $old_header=""; //outside 
   while ($row=mysql_fetch_assoc($query)){
     if ($old_header!=$row['value']){  //table1.value 
         $old_header=$row['value'];
         echo "<h1>Category: ".$old_header."</h1>";
     } ...

the group by will merge all queries that have same table1.value which is not what you want.

score 1 · Answer 2 · edited May 23 '17 at 10:00

1

Normally this is done in code (rather than in sql) but GROUP_CONCAT might work. Have a look at MySQL Results as comma separated list

edited May 23 '17 at 10:00

Community

1
1

answered May 25 '11 at 01:17

James Scriven

7,784
1
32
36

join query and grouping to show result

2 Answers2