SQL statement with Loop and concatenation of single column

Discussion in 'MySQL' started by stussy, Mar 1, 2012.

0
  1. #1
    Hi,

    Suppose I have 2 tables:

    table1:
    [TABLE="class: grid, width: 250, align: left"]
    [TR]
    [TD]tid[/TD]
    [TD]tdesc[/TD]
    [/TR]
    [TR]
    [TD]1[/TD]
    [TD]Description 1[/TD]
    [/TR]
    [TR]
    [TD]2[/TD]
    [TD]Description 2[/TD]
    [/TR]
    [TR]
    [TD]3[/TD]
    [TD]Description 3[/TD]
    [/TR]
    [/TABLE]






    table2:
    [TABLE="class: grid, width: 500, align: left"]
    [TR]
    [TD]sid[/TD]
    [TD]tid[/TD]
    [TD]detail[/TD]
    [/TR]
    [TR]
    [TD]1[/TD]
    [TD]1[/TD]
    [TD]Foo[/TD]
    [/TR]
    [TR]
    [TD]2[/TD]
    [TD]1[/TD]
    [TD]Bar[/TD]
    [/TR]
    [TR]
    [TD]3[/TD]
    [TD]2[/TD]
    [TD]Lorem[/TD]
    [/TR]
    [TR]
    [TD]4[/TD]
    [TD]3[/TD]
    [TD]Need[/TD]
    [/TR]
    [TR]
    [TD]5[/TD]
    [TD]3[/TD]
    [TD]Help[/TD]
    [/TR]
    [/TABLE]









    What SQL statement can I use to get this result?
    outputTable:
    [TABLE="class: grid, width: 500, align: left"]
    [TR]
    [TD]tid[/TD]
    [TD]tdesc[/TD]
    [TD]detail[/TD]
    [/TR]
    [TR]
    [TD]1[/TD]
    [TD]Description 1[/TD]
    [TD]Foo, Bar[/TD]
    [/TR]
    [TR]
    [TD]2[/TD]
    [TD]Description 2[/TD]
    [TD]Lorem[/TD]
    [/TR]
    [TR]
    [TD]3[/TD]
    [TD]Description 3[/TD]
    [TD]Need, Help[/TD]
    [/TR]
    [/TABLE]
    Thank you in advance!
     
    stussy, Mar 1, 2012 IP
  2. kids

    kids Active Member

    Messages:
    411
    Likes Received:
    4
    Best Answers:
    2
    Trophy Points:
    68
    #2
    Try this:

    "SELECT t1.*,t2.detail FROM table1 t1 LEFT JOIN table2 t2 USING (tid)"

    ... and with case as 3rd row, you can solve by loop with condition it is not too difficult to do
     
    Last edited: Mar 2, 2012
    kids, Mar 2, 2012 IP
  3. stussy

    stussy Peon

    Messages:
    2
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    @kids: Thank you for your prompt response.
    But the statement did not concatenated the detail field, instead it returned 2 rows for tid=1
     
    stussy, Mar 2, 2012 IP
  4. kids

    kids Active Member

    Messages:
    411
    Likes Received:
    4
    Best Answers:
    2
    Trophy Points:
    68
    #4
    I think It's hard to do in only single SQL statement. You must done with two sections:

    I have done with PHP:
    
$sql1 = "SELECT * FROM T1";
//... data connection execute ... 
while ($data1 = mysql_fetch_array($result1)){
$data_str=$data['tid']." - ".$data['desc'];
$sql2 = "SELECT * FROM T2 WHERE T2.tid='".$data['tid']."'";
// fetch num rows
if ($rows!=0){
$details="";$i=0;
while ($data2=mysql_fetch_array($result2)){
$details.=($i==0)?$data2['detail']:", ".$data2['detail'];
}
$data_str.=" - ".$details;
}
}
    PHP:
     
    kids, Mar 2, 2012 IP