How to get only all rows that apply this WHERE clause?-CodePudding

I have two tables, tasks and views, with the following structure:

tasks
-- id
-- status
views
-- id
-- taskid (FK of tasks.id)
-- status

And the tasks table, has a row with id = 1 and status = 1, whilst the views table has two rows with id = 1, taskid = 1, status = 1 and id = 2, taskid = 1, status = 0.

When I try to get all the tasks id that have all its views status set to 1 and the task's status itself set to 1 too and only, then I get in return a row with task id = 1 because view number 1 is set to 1 and view number 2 is set to 0.

So basically, what I need is an SQL statement that returns only one row for each task that has all its views and task status set to 1 (tasks.status = 1, views.status = 1) and only when that happens, and if any of the tasks' views is set to something different than 1, then the SQL statement doesn't return it.

Here is my SQL Statement so far which kind of works, but there is still something I am missing because it doesn't work as expected. Sorry if something isn't clear!

SELECT tasks.id FROM tasks JOIN views ON tasks.id = views.taskid WHERE tasks.status = 1 AND views.status = 1;

CodePudding user response：

Join tasks to a query that uses aggregation to return only the taskids with min status set to 1 (which means there is no 0):

SELECT t.id 
FROM tasks t 
INNER JOIN (
  SELECT taskid
  FROM views 
  GROUP BY taskid
  HAVING MIN(status) = 1
) v ON v.taskid = t.id
WHERE t.status = 1;

CodePudding user response：

with t as (select taskid,
 count(status) status_cnt,
 sum(case when status = 1 then 1 else 0 end) as status_1_cnt
 from views 
 group by taskid),
t2 as (select taskid from t where status_1_cnt > 0 and status_cnt = status_1_cnt)
select tasks.id from tasks join t2 on tasks.id = t2.taskid and status = 1

so If I am reading your question correctly you want all of the statuses in the view to be 1 per taskid. so I would count the view statuses and compare that count to where the view status is 1. (the case statement).

then just join this to the task table where the task status is 1

(although I like @forpas answer better)

CodePudding user response：

Conceptually you only want to join on the records in the views table where status = 1, so like this:

SELECT A.id
FROM
    tasks A
    INNER JOIN
    (
        SELECT
            tasks.taskid 
        FROM
            views 
        WHERE
            view.status = 1
    ) B
    ON A.id = B.taskid

Although the syntax is less intuitive, this is equivalent and shorter:

SELECT 
    tasks.id
FROM 
    tasks 
    JOIN views
    ON tasks.id = views.taskid
    AND view.status = 1
WHERE
    tasks.status = 1

This should also work, for a different reason (more like a trick):

SELECT A.id
FROM
    tasks A
    INNER JOIN views B
    ON a.id = b.taskid
    and a.status = b.status
WHERE
    A.status = 1

This might be more stringent of a test if thats what you need (the matching records in views with the requirement that no other records with status = 0 exist in views) - but I would like to avoid this style of using a correlated subquery in real life if the tables are of an significant size:

SELECT A.id
FROM
    tasks A
    INNER JOIN views B
    ON A.id = B.taskid
WHERE
    A.status = 1
    AND B.status = 1
    AND NOT EXISTS (SELECT * FROM views c 
                     WHERE c.taskid = b.taskid and c.status = 0)

Finally this is a solution that thinks conceptually more in terms of the intersection of the sets:

SELECT A.id
FROM
    tasks a
    INNER JOIN views a
    ON A.id = b.taskid
    AND b.status = 1
    LEFT JOIN views c
    ON a.id = c.taskid
    AND c.status = 0
WHERE
    A.status = 1
    AND c.status is null

I just saw that forpas has just shown a different but very good solution using aggregation with a min() clause to select only the appropriate records from views for use in joining to tasks which seems like it may be the winner to me :)

CodePudding user response：

If I understand you correctly, you want to get id of task , if and ONLY if it's status = 1, and there are particular records in views table with ONLY same status = 1.

Then your query would be like this:

select tasks.id 
from tasks 
where status =1 and not exists(
    select 1 
    from views 
    where  taskid=tasks.id and views.status!=tasks.status)

Check demo https://www.db-fiddle.com/f/cQfQMx5LGJN516ND2iVj8y/3