For the simplification of the problem, lets say I have 2 tables: user

• id: int

ticket

• id: int
• user_id: int
• marked: bool

With the given example data:

user

ticket

User 1 and 2 have marked tickets. User 3 has 1 unmarked ticket. User 4 has no tickets. User 5 has 2 unmarked tickets.

And I need a query that returns tickets with id 7, 8 and 9 - the tickets of users who don't have marked tickets.

I've written the following query:

SELECT * FROM ticket t
INNER JOIN user u ON t.user_id=u.id
INNER JOIN ticket tt ON u.id = tt.user_id
WHERE tt.marked = false;

But it doesn't works as expected. I don't want to use subqueries to exclude users with marked tickets. Can this be done fully with JOINs? So it happens that I'm not that familiar with JOIN clauses.

CodePudding user response：

This assumes marked is an int. You may need to adjust your query to convert your bool data type.

with a as (
    select t.user_id
    , max(t.marked) as marked
    from ticket t
    group by t.user_id
)

select t.*
from ticket t
  inner join a on a.user_id = t.user_id
where a.marked = 0

I deliberately omitted user since it adds no value.

CodePudding user response：

I'm hesitant to answer without more info, but subqueries may be helpful. set up and post a repel for us to try it out.

If you're not familiar, here's a brief video on subqueries. https://www.youtube.com/watch?v=GpC0XyiJPEo&t=417s