1

I have below scenario:

    create table t1 (id int, title varchar(20), description varchar(20));
    create table t2 (id int, info1 varchar(20), info2 varchar(20));
    create table t3 (id1 int auto_increment, info varchar(20), id int, primary key(id1));
    create table t4 (num int, name varchar(20), some varchar(20), lid int, sid int);
    create table t5 (sid int, sname varchar(20), addr varchar(50));
    create table t6 (url varchar(30), id int);

    insert into t1 values (1, "title1", "desc1");
    insert into t2 values (1, "test info1", "test info2");
    insert into t3 (id1, info, id) values (1, "info 1", 1), (2, "info 2", 1), (3, "info 3", 1);
    insert into t4 values (2121, 'name1', 'test1', '11', 1), (2323, 'name2', 'test2', '12', 2), (2232, 'name3', 'test3', '13', 3);
    insert into t5 values (1, 'sname1', 'addr1'), (2, 'sname2', 'addr2'), (3, 'sname3', 'addr3');
    insert into t6 values ('url1', 1), ('url2', 1), ('url3', 1);

    mysql> select * from t1;
    +------+--------+-------------+
    | id   | title  | description |
    +------+--------+-------------+
    |    1 | title1 | desc1       |
    +------+--------+-------------+

    mysql> select * from t2;
    +------+------------+------------+
    | id   | info1      | info2      |
    +------+------------+------------+
    |    1 | test info1 | test info2 |
    +------+------------+------------+

    mysql> select * from t3;
    +-----+--------+------+
    | id1 | info   | id   |
    +-----+--------+------+
    |   1 | info 1 |    1 |
    |   2 | info 2 |    1 |
    |   3 | info 3 |    1 |
    +-----+--------+------+

    mysql> select * from t4;
    +------+-------+-------+------+------+
    | num  | name  | some  | lid  | sid  |
    +------+-------+-------+------+------+
    | 2121 | name1 | test1 |   11 |    1 |
    | 2323 | name2 | test2 |   12 |    2 |
    | 2232 | name3 | test3 |   13 |    3 |
    +------+-------+-------+------+------+

    mysql> select * from t5;
    +------+--------+-------+
    | sid  | sname  | addr  |
    +------+--------+-------+
    |    1 | sname1 | addr1 |
    |    2 | sname2 | addr2 |
    |    3 | sname3 | addr3 |
    +------+--------+-------+

    mysql> select * from t6;
    +------+------+
    | url  | id   |
    +------+------+
    | url1 |    1 |
    | url2 |    1 |
    | url3 |    1 |
    +------+------+

And I am running below query to get values from the above tables:

    select
    t1.id,
    t1.title,
    t1.description,
    t2.id,
    t2.info1,
    t2.info2,
    t3.id1,
    t3.info,
    t4.num,
    t4.name,
    t4.some,
    t4.lid,
    t4.sid,
    t5.sname,
    t5.addr,
    GROUP_CONCAT(DISTINCT(t6.url) SEPARATOR ',') as url
    from t1
    join t2 on t2.id = t1.id
    join t3 on t3.id = t1.id
    join t4 on t4.sid = t3.id1
    join t5 on t5.sid = t3.id1
    join t6 on t6.id = t1.id
    group by t1.id

And I am getting below result:

+------+--------+-------------+------+------------+------------+-----+--------+------+-------+-------+------+------+--------+-------+----------------+
| id   | title  | description | id   | info1      | info2      | id1 | info   | num  | name  | some  | lid  | sid  | sname  | addr  | url            |
+------+--------+-------------+------+------------+------------+-----+--------+------+-------+-------+------+------+--------+-------+----------------+
|    1 | title1 | desc1       |    1 | test info1 | test info2 |   1 | info 1 | 2121 | name1 | test1 |   11 |    1 | sname1 | addr1 | url1,url2,url3 |
+------+--------+-------------+------+------------+------------+-----+--------+------+-------+-------+------+------+--------+-------+----------------+

But instead of this I am expecting below result:

+------+--------+-------------+------+------------+------------+-----+--------+------+-------+-------+------+------+--------+-------+----------------+
| id   | title  | description | id   | info1      | info2      | id1 | info   | num  | name  | some  | lid  | sid  | sname  | addr  | url            |
+------+--------+-------------+------+------------+------------+-----+--------+------+-------+-------+------+------+--------+-------+----------------+
|    1 | title1 | desc1       |    1 | test info1 | test info2 |   1 | info 1 | 2121 | name1 | test1 |   11 |    1 | sname1 | addr1 | url1,url2,url3 |
|    1 | title1 | desc1       |    1 | test info1 | test info2 |   2 | info 2 | 2323 | name2 | test2 |   12 |    2 | sname2 | addr2 | url1,url2,url3 |
|    1 | title1 | desc1       |    1 | test info1 | test info2 |   3 | info 3 | 2232 | name3 | test3 |   13 |    3 | sname3 | addr3 | url1,url2,url3 |
+------+--------+-------------+------+------------+------------+-----+--------+------+-------+-------+------+------+--------+-------+----------------+

Please help me. How can I get the correct output?

2

Change

group by t1.id

to

group by t1.id, t3.id1
              ^^^^^^^^

Output:

| ID |  TITLE | DESCRIPTION |      INFO1 |      INFO2 | ID1 |   INFO |  NUM |  NAME |  SOME | LID | SID |  SNAME |  ADDR |            URL |
|----|--------|-------------|------------|------------|-----|--------|------|-------|-------|-----|-----|--------|-------|----------------|
|  1 | title1 |       desc1 | test info1 | test info2 |   1 | info 1 | 2121 | name1 | test1 |  11 |   1 | sname1 | addr1 | url3,url2,url1 |
|  1 | title1 |       desc1 | test info1 | test info2 |   2 | info 2 | 2323 | name2 | test2 |  12 |   2 | sname2 | addr2 | url3,url2,url1 |
|  1 | title1 |       desc1 | test info1 | test info2 |   3 | info 3 | 2232 | name3 | test3 |  13 |   3 | sname3 | addr3 | url3,url2,url1 |

Here is a SQLFiddle demo

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.