CODEWITHSUNDEEP
mysql
SimonH
SimonH

Reputation: 1425

How to write this query in MySQL?

I have the following tables:

project(pid integer primary key, title varchar(30), finished boolean)
action(aid integer primary key, title varchar(30), finished boolean)
project_action(pid integer, aid integer, primary key(pid, aid))

A project can consist of multiple actions and an action can only belong to none or one project. Thus the cardinatlity is (project) 0..1 <--> * (action).

How can I get just the projects, whose actions are all finished?

I tried this, but that already gives me a project if only one of its actions is finished: 

SELECT pid FROM project WHERE pid IN (SELECT DISTINCT pid FROM project_action WHERE aid IN (SELECT aid FROM action WHERE action.finished = true));

Upvotes: 2

Views: 85

Answers (5)

F.bernal
F.bernal

Reputation: 2694

SELECT p.pid FROM project p WHERE p.pid NOT IN 
  (SELECT pa.pid FROM project_action pa 
   JOIN action a ON pa.aid = a.aid 
   WHERE a.finished = FALSE)

Upvotes: 0

Serg Stetsuk
Serg Stetsuk

Reputation: 429

Try to select just projects without unfinished actions. You should keep in mind that this approach will give you the projects with no actions at all too.

SELECT pid FROM project WHERE pid NOT IN
  (SELECT DISTINCT pid FROM project_action WHERE aid IN 
     (SELECT aid FROM action WHERE action.finished = false)
  );

Upvotes: 0

DRapp
DRapp

Reputation: 48179

Although somewhat unclear about the intent of your table content, I think your structures might be a little off. Let me make a simple example of a car repair (and I am by no means an auto-body person).

Project, Repair Person "A" car.
Pre-clean
Pull dent / repair parts
Apply any filler
Sand it
Paint

IF the "Actions" are going to be consistent among multiple projects, then that table will not have the "finished" boolean flag. The flag would be specific to the one project being worked on. If I had 10 cars, I could have 3 finished completely and 7 in the works somewhere along the different phases. So, that said, I would have expected a structure more like

project(pid integer primary key, title varchar(30), finished boolean)
action(aid integer title varchar(30))
project_action(pid integer, aid integer, finished boolean, primary key(pid, aid))

Having said all that, and staying with your original structures, you could have a project with per example, 2, 3 or more actions that need to be performed on it. If ANY action is incomplete, then entire project is incomplete. I would also have an index on your "Action" table based on (pid, aid, finished)

Then do a NOT EXISTS of finished = false

SELECT
      p.pid,
      p.title
   from
      project p
   where
      NOT EXISTS ( select pa.pid
                     from project_action pa
                        join action a on pa.aid = a.aid
                        AND a.finished = false
                     where pa.pid = p.pid )

This is basically stating... give me all projects where there are no "FALSE" finished actions pending for the project for whatever the current project is. The EXISTS (or NOT EXISTS) stops as soon as if finds a record that qualifies the condition. So, if you had 10 things incomplete for a project, as soon as ONE was found, it is done with the qualifying WHERE and accepts or rejects the project record and moves on to the next project.

Upvotes: 1

Joey Yandle
Joey Yandle

Reputation: 141

I would join the tables:

SELECT project.pid FROM project, project_action, action 
WHERE project.pid = project_action.pid 
AND project_action.aid = action.aid 
AND action.finished = TRUE

Upvotes: 0

Poonam
Poonam

Reputation: 549

Try this -

SELECT project.pid FROM project LEFT JOIN project_action ON project_action.pid = project.pid LEFT JOIN action ON project_action.aid = action.aid WHERE action.finished = true

Upvotes: 0

Related Questions