CODEWITHSUNDEEP
mysql
The Shizy
The Shizy

Reputation: 45

Adding an extra condition to SQL query

I'm trying to add an extra layer to my SQL query which looks like this:

SELECT 
  COUNT(*) totalCount 
FROM 
  ts_room 
WHERE 
  NOT EXISTS (
    SELECT 1
    FROM ts_roompref 
      JOIN ts_request 
      ON ts_roompref.request_id = ts_request.id
      AND day_id = 1 
      AND period_id = 1
    WHERE 
      ts_room.id = ts_roompref.room_id)

What I would like it to do is check that a row for the matching request.id does not exist in a table called ts_allocation. This is what I've attempted so far but it doesn't seem to be working:

SELECT 
  COUNT(*) totalCount 

FROM 
  ts_room 

WHERE 
  NOT EXISTS (
    SELECT 1
    FROM ts_roompref 
      JOIN ts_request 
      ON ts_roompref.request_id = ts_request.id
      AND day_id = 1 
      AND period_id = 1
    WHERE 
      ts_room.id = ts_roompref.room_id)

AND NOT EXISTS (
    SELECT 1
    FROM ts_roompref 
      JOIN ts_allocation
      ON ts_roompref.request_id = ts_allocation.request_id
      AND ts_allocation.status = "Allocated" 
    WHERE 
      ts_room.id = ts_roompref.room_id)
  )

Any ideas? I've included my SQL Fiddle here: http://sqlfiddle.com/#!2/4540d/2

Upvotes: 1

Views: 83

Answers (1)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726479

I think the problem has to do with the use of aliases: once I added alias names to the tables of your query, the query ran and returned some values (link to your modified sqlfiddle):

SELECT COUNT(*) totalCount 
FROM ts_room rm
WHERE 
NOT EXISTS (
    SELECT 1
    FROM ts_roompref rp
    JOIN ts_request rq ON rp.request_id = rq.id AND day_id = 1 AND period_id = 1
    WHERE rm.id = rp.room_id)
AND NOT EXISTS (
    SELECT 1
    FROM ts_roompref rp
    JOIN ts_allocation a ON rp.request_id = a.request_id AND a.status = "Allocated" 
    WHERE  rm.id = rp.room_id)

Upvotes: 1

Related Questions