1

Database sample ER Diagram

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

0

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

|improve this answer|||||
-1

Unless I misunderstand your question, you just want to join more than 1 table like on this other post?

https://stackoverflow.com/questions/14995000/sql-inner-join-more-than-two-tables

|improve this answer|||||

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.