CODEWITHSUNDEEP
mysqlsql
Asim Zaidi
Asim Zaidi

Reputation: 28344

join query and grouping to show result

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.

Upvotes: 2

Views: 94

Answers (2)

James Scriven
James Scriven

Reputation: 8154

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

Upvotes: 1

Dreaded semicolon
Dreaded semicolon

Reputation: 2294

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.

Upvotes: 2

Related Questions