Mysql – Failing to get multiple values using join

MySQLmysql-5.5

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?

Best Answer

Change

group by t1.id

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

Related Solutions

Mysql – Drop auto increment hack w/o alter table

I tried something similar just now

Here is MySQL for My PC

mysql> select * from information_schema.global_variables where variable_name='datadir' or variable_name like 'versio%';
+-------------------------+------------------------------+
| VARIABLE_NAME           | VARIABLE_VALUE               |
+-------------------------+------------------------------+
| VERSION_COMMENT         | MySQL Community Server (GPL) |
| VERSION                 | 5.5.12-log                   |
| VERSION_COMPILE_MACHINE | x86                          |
| DATADIR                 | C:\MySQL_5.5.12\data\        |
| VERSION_COMPILE_OS      | Win64                        |
+-------------------------+------------------------------+
5 rows in set (0.00 sec)

I will run this using MyISAM

Step 01) create a table called 'rolando'
Step 02) insert 'dominique' and 'diamond'
Step 03) copy the table structure to 'pamela'
Step 04) alter 'pamela' to not have auto_increment
Step 05) In DOS, copy rolando.MYD to pamela.MYD
Step 06) run REPAIR TABLE pamela; (Rebuild pamela.MYI)
Step 07) run SELECT COUNT(1) FROM pamela;
Step 08) run SHOW CREATE TABLE pamela\G
Step 09) run SELECT * FROM pamela;
Step 10) insert 'carlik' into pamela
Step 11) run SELECT * FROM pamela;

Let's see if these steps are kosher.

Here are Steps 1-4

mysql> drop table if exists rolando;
Query OK, 0 rows affected (0.02 sec)

mysql> drop table if exists pamela;
Query OK, 0 rows affected (0.00 sec)

mysql> create table rolando
    -> (
    ->     name varchar(20),
    ->     id int not null auto_increment,
    ->     primary key (id)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into rolando (name) values ('dominique'),('diamond');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from rolando;
+-----------+----+
| name      | id |
+-----------+----+
| dominique |  1 |
| diamond   |  2 |
+-----------+----+
2 rows in set (0.00 sec)

mysql> create table pamela like rolando;
Query OK, 0 rows affected (0.05 sec)

mysql> show create table rolando\G
*************************** 1. row ***************************
       Table: rolando
Create Table: CREATE TABLE `rolando` (
  `name` varchar(20) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show create table pamela\G
*************************** 1. row ***************************
       Table: pamela
Create Table: CREATE TABLE `pamela` (
  `name` varchar(20) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.02 sec)

mysql> alter table pamela modify id int(11) unsigned not null;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table pamela\G
*************************** 1. row ***************************
       Table: pamela
Create Table: CREATE TABLE `pamela` (
  `name` varchar(20) DEFAULT NULL,
  `id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select count(1) from pamela;
+----------+
| count(1) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)

mysql>

Here is Step 6

C:\>copy C:\MySQL_5.5.12\data\test\rolando.MYD C:\MySQL_5.5.12\data\test\pamela.MYD
        1 file(s) copied.

C:\>

Here are the rest of the Steps starting at Step 7

mysql> repair table pamela;
+-------------+--------+----------+------------------------------------+
| Table       | Op     | Msg_type | Msg_text                           |
+-------------+--------+----------+------------------------------------+
| test.pamela | repair | warning  | Number of rows changed from 0 to 2 |
| test.pamela | repair | status   | OK                                 |
+-------------+--------+----------+------------------------------------+
2 rows in set (0.03 sec)

mysql> select count(1) from pamela;
+----------+
| count(1) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

mysql> insert into pamela (name,id) values ('carlik',3);
Query OK, 1 row affected (0.00 sec)

mysql> select * from pamela;
+-----------+----+
| name      | id |
+-----------+----+
| dominique |  1 |
| diamond   |  2 |
| carlik    |  3 |
+-----------+----+
3 rows in set (0.00 sec)

mysql>

Dangerous game, isn't it ???

Guess what? Stuff like this is actually published in "High Performance MySQL : Optimization, Backups, Replication, and more", Pages 146-148 under the Subheading Speeding Up ALTER TABLE. Page 147 Paragraph 1 says:

The technique we are about to demonstrate is unsupported, undocumented, and may not work. Use it at your risk. We advise you to back up you data first!

I also had an earlier post when someone ask a similar question : Can I rename the values in a MySQL ENUM column in one query?

You got guts, @atxdba !!!

Mysql – Use MySQL to regularly do multi-way joins on 100+ GB tables

Have you tried piling more data and benchmarking it? 100K rows is inconsequential. Try 250M or 500M like you're expecting you'll need to handle and see where the bottlenecks are.

An RDBMS can do a lot of things if you pay careful attention to the limitations and try and work with the strengths of the system. They're exceptionally good at some things, and terrible at others, so you will need to experiment to be sure it's the right fit.

For some batch processing jobs, you really cannot beat flat files, loading the data into RAM, smashing it around using a series of loops and temporary variables, and dumping out the results. MySQL will never, ever be able to match that sort of speed, but if tuned properly and used correctly it can come within an order of magnitude.

What you'll want to do is investigate how your data can be partitioned. Do you have one big set of data with too much in the way of cross-links to be able to split it up, or are there natural places to partition it? If you can partition it you won't have one table with a whole pile of rows, but potentially many significantly smaller ones. Smaller tables, with much smaller indexes, tend to perform better.

From a hardware perspective, you'll need to test to see how your platform performs. Sometimes memory is essential. Other times it's disk I/O. It really depends on what you're doing with the data. You'll need to pay close attention to your CPU usage and look for high levels of IO wait to know where the problem lies.

Whenever possible, split your data across multiple systems. You can use MySQL Cluster if you're feeling brave, or simply spin up many independent instances of MySQL where each stores an arbitrary portion of the complete data set using some partitioning scheme that makes sense.

Best Answer

Related Solutions

Mysql – Drop auto increment hack w/o alter table

Mysql – Use MySQL to regularly do multi-way joins on 100+ GB tables

Related Question