Mysql – Joining 4 tables of which one is unrelated

database-designjoin;MySQLperformancequery-performance

I have 4 tables as follows.

I want to find a wholesum resultset, limited to 25 records, in the following way:

Users from Table1 based on a condition
And those Users should not be in Table2 [Non restricted users]
Join those records to Table3 on Table1.Group_id = Table3.Group_MEMBER_ID where Table3.GroupName = 'someValue'
Join Table1 with Table4 on Table1.UserName = Table4.UserName

Am currently using two queries and am using UNION. Something like this:

      ( SELECT  Table_4.DeviceName
            from  Table1
            left join  Table2 on Table1.UserName = Table2.UserName
            inner join  Table4 on Table1.UserName = Table4.UserName
            where  Table1.some_column = 'someValue'
              and  Table2.UserName = null
            limit  25
      )
UNION
      ( SELECT  Table4.DeviceName
            from  Table1
            inner join  on Table1.Group_ID = Table3.GROUP_MEMBER_ID
            inner join  Table4 Table1.UserName = Table4.UserName
            where  Table3.GroupName='someValue'
              and  Table1.some_column = 'someValue'
            limit  25
      );

The point is, I cant limit the wholesum resultset to 25 records. I should use LIMIT to each query. Is there a way to merge it into a single query so that I can limit the wholesum resultset to 25?

Note: PL/SQL and CTE not available in my Development System and hence am not able to use it.

Best Answer

Problem 1: LIMIT is somewhat meaningless without ORDER BY to indicate which 25.

Problem 2: Don't say x=null, it won't do what you think. Instead do x IS NULL.

Problem 3: Do you really want a "cross join"? That is, a JOIN without an ON clause, and hence gets all combinations of the two tables?

Problem 4: Well, I did not check the rest of what you have.

Back to your question... The pattern is

( SELECT ... ORDER BY ... LIMIT 25 )
UNION ALL  -- or DISTINCT, depending on your understanding of the data
( SELECT ... ORDER BY ... LIMIT 25 )
ORDER BY ... -- Yes, again
LIMIT 25;

If you need an offset of, say 100, then:

( ... LIMIT 125 )
UNION ...
( ... LIMIT 125 )
ORDER BY ...
LIMIT 100, 25;

Related Solutions

Mysql – How to join two table and show one query result in MySQL

Here is the query you need:

SELECT name FROM
(
    SELECT name FROM table1
    UNION
    SELECT name FROM table2
) A;

Here is some sample code based on your question:

use test
drop table if exists table1;
drop table if exists table2;
create table table1
(
    id int not null auto_increment,
    name varchar(10),
    primary key (id)
);
create table table2 like table1;
insert into table1 (name) values ('A'),('B'),('C');
insert into table2 (name) values ('C'),('D'),('E');
SELECT name FROM
(
    SELECT name FROM table1
    UNION
    SELECT name FROM table2
) A;

Here is the execution of that sample code:

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

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

mysql> create table table1 (
    ->     id int not null auto_increment,
    ->     name varchar(10),
    ->     primary key (id)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> create table table2 like table1;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into table1 (name) values ('A'),('B'),('C');
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into table2 (name) values ('C'),('D'),('E');
Query OK, 3 rows affected (0.11 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT name FROM (SELECT name FROM table1
    -> UNION SELECT name FROM table2) A;
+------+
| name |
+------+
| A    |
| B    |
| C    |
| D    |
| E    |
+------+
5 rows in set (0.00 sec)

mysql>

Give it a Try !!!

Mysql – poor and/or unreliable InnoDB performance

innoDB

is bad for dml statement, #MyISAM is better. Innodb is try to commit all INSERTing data to disk and returns success after it pined.

when you need a big insert data you can avoid auto-commit=0 to get faster. or you can check this for hint.

Best Answer

Related Solutions

Mysql – How to join two table and show one query result in MySQL

Mysql – poor and/or unreliable InnoDB performance

innoDB

Related Question