CODEWITHSUNDEEP
sqlt-sql
LJ.
LJ.

Reputation: 885

One to many relationship - returning only results where ALL rows in a linked table match a certain criterion without using a correlated subquery

I have the following tables:

A message is related to an order, which can have many users that reviewed it. Each user may or may not have a special role assigned. I want to return only those messages that haven't been reviewed by ANY users assigned a special role. I wrote a query to do this that relies on a correlated subquery (inner SELECT refers to message.order_id from outer SELECT). This is legal in T-SQL but is very slow.

What is a more efficient way to write this type of query?

select message.id
  from message, order
  where order.id = message.order_id
  and not exists (select *
                    from user, review
                    where user.role_id is not null and
                          user.id = review.user_id and
                          review.order_id = message.order_id)

Upvotes: 0

Views: 323

Answers (4)

user359040
user359040

Reputation:

Your existing query is pretty good, but the join to orders isn't necessary - try:

select message.id
  from message
  where not exists (select null
                    from user, review
                    where user.role_id is not null and
                          user.id = review.user_id and
                          review.order_id = message.order_id)

Upvotes: 0

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

Yet another take on it:

SELECT message.id
  FROM message
INNER JOIN ORDER
  ON (order.id = message.order_id)
LEFT OUTER JOIN review
  ON (review.order_id = message.order_id)
LEFT OUTER JOIN user
  ON (user.id = review.user_id)
WHERE review.order_id IS NOT NULL AND
      user.role_id IS NULL;

Share and enjoy.

Upvotes: 1

Nazarii Bardiuk
Nazarii Bardiuk

Reputation: 4342

select id 
 from message 
where id not in(
    select message.id
     from message
    inner join review
       on review.order_id = message.order_id
    inner join user
       on user.user_id = review.user_id
    where user.role_id is not null)

In where clause I drop all messages that have any review from with role assigned. And I think that table order is needless in this query

Upvotes: 1

Fosco
Fosco

Reputation: 38526

Give this a shot and let me know what you think:

select * from message where id not in (

    select m.id
    from message m
    join order o on m.order_id = o.order_id
    join review r on o.order_id = r.order_id
    join user u on r.user_id = u.user_id
                and u.role_id is not null

)

Upvotes: 1

Related Questions