Mysql – fetch data from multiple tables using single query

join;MySQL

G'day everyone,

This is my first question to this community, as I am stuck for this task in the company where I am working as part time intern. I am very new to working with SQL and can't figure out the solution.

Consider we have following 5 tables in the DB: (removing irrelevant fields)

(image of ERD also attached)

desc jobs;
+-----------------------+--------------+------+-----+---------+----------------+
| Field                 | Type         | Null | Key | Default | Extra          |
+-----------------------+--------------+------+-----+---------+----------------+
| id                    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name                  | varchar(255) | YES  |     | NULL    |                |
| job_queue_id          | int(11)      | YES  | MUL | NULL    |                |
| job_action_id         | int(11)      | YES  |     | NULL    |                |
| user_id               | int(11)      | YES  | MUL | NULL    |                |
+-----------------------+--------------+------+-----+---------+----------------+

desc organisations;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| name       | varchar(255) | YES  |     | NULL    |                |
| enabled    | tinyint(1)   | YES  |     | 1       |                |
+------------+--------------+------+-----+---------+----------------+

desc job_queues;
+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| id              | int(11)      | NO   | PRI | NULL    | auto_increment |
| name            | varchar(255) | YES  |     | NULL    |                |
| organisation_id | int(11)      | YES  | MUL | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+

desc attached_files;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| kind       | int(11)      | YES  |     | NULL    |                |
| location   | varchar(255) | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

desc job_attached_files;
+------------------+----------+------+-----+---------+----------------+
| Field            | Type     | Null | Key | Default | Extra          |
+------------------+----------+------+-----+---------+----------------+
| id               | int(11)  | NO   | PRI | NULL    | auto_increment |
| job_id           | int(11)  | YES  | MUL | NULL    |                |
| attached_file_id | int(11)  | YES  | MUL | NULL    |                |
+------------------+----------+------+-----+---------+----------------+

Now I have to fetch
jobs.jobid, jobs.name,
attached_files.id, attached_files.kind, attached_files.location
where the job has the organisation name "organisation1" which has ID "1".

The job table has job_queues id job_queues has got organisation id, organisations has got organisation name.

In simple words, I need to get all jobs from job table (and its relevant data from attached_files) where organisation name = organisation 1.

I don't know if I have stated and explained the question correctly and using right/professional approach, but it would be highly appreciated if you could figure out a single mega query to join all these tables and fetch the desired result.

Cheers,
Khan

Best Answer

For the above requirement, following query should work.

SELECT j.jobid, j.name, af.id, af.kind, af.location 
FROM jobs j
INNER JOIN job_queues jq ON jq.id = j.job_queue_id
INNER JOIN organisations o ON o.id = jq.organisation_id
LEFT OUTER JOIN job_attached_files jaf ON jaf.job_id = j.id
INNER JOIN attached_files af ON af.id = jaf.attached_file_id
WHERE o.Id = 1

P.S: I am posting this query without any testing, so if there are any compilation errors or any unwanted results, please correct the query if you want or notify me, so that I can edit my answer accordingly

Related Solutions

Mysql – Unexplained InnoDB timeouts

I know this is really late, but you really need to capture the output of SHOW ENGINE INNODB STATUS; during that query to see why it's waiting.

If it happens a lot during a specific time, it would be easy to just grab that output every x seconds and hope you capture it (or perhaps artificially generate the load).

Mysql – get column from too many tables in thesql

If all the tables use the MyISAM Storage Engine and have the same table structure, I have some good news for you.

You can create a single table that consumes no additional space except a .frm file and some mapping info. The key is to take advantage of the MERGE (MRG_MyISAM) Storage Engine.

Here is how you can do this:

CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4) INSERT_METHOD=LAST;

Using this method, you can query the 4 tables at the same time like this:

SELECT ReqF FROM XMerge WHERE EmpName='John';

Was that simple, or what ???

In your case, you have 75 tables. You would do this:

CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4,X5,X6,X7,X8,X9,
X10,X11,X12,X13,X14,X15,X16,X17,X18,X19,
X20,X21,X22,X23,X24,X25,X26,X27,X28,X29,
X30,X31,X32,X33,X34,X35,X36,X37,X38,X39,
X40,X41,X42,X43,X44,X45,X46,X47,X48,X49,
X50,X51,X52,X53,X54,X55,X56,X57,X58,X59,
X60,X61,X62,X63,X64,X65,X66,X67,X68,X69,
X70,X71,X72,X73,X74,X75) INSERT_METHOD=LAST;
SELECT ReqF FROM XMerge WHERE EmpName='John';

The beauty of this is that creating a MERGE table takes milliseconds. Just make sure every table has an index on EmpName. Better to do 75 indexed lookups that 75 full table scans. If there is no index on EmpName, you need to do this:

ALTER TABLE X1 ADD UNIQUE KEY (EmpName);
ALTER TABLE X2 ADD UNIQUE KEY (EmpName);
.
.
.
ALTER TABLE X75 ADD UNIQUE KEY (EmpName);
CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4,X5,X6,X7,X8,X9,
X10,X11,X12,X13,X14,X15,X16,X17,X18,X19,
X20,X21,X22,X23,X24,X25,X26,X27,X28,X29,
X30,X31,X32,X33,X34,X35,X36,X37,X38,X39,
X40,X41,X42,X43,X44,X45,X46,X47,X48,X49,
X50,X51,X52,X53,X54,X55,X56,X57,X58,X59,
X60,X61,X62,X63,X64,X65,X66,X67,X68,X69,
X70,X71,X72,X73,X74,X75) INSERT_METHOD=LAST;
SELECT ReqF FROM XMerge WHERE EmpName='John';

Give it a Try !!!

Best Answer

Related Solutions

Mysql – Unexplained InnoDB timeouts

Mysql – get column from too many tables in thesql

Related Question