Adding an extra condition to SQL query

Question

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

Sergey Kalinichenko · Accepted Answer · 2013-02-16 11:29:17Z

1

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)

answered Feb 16, 2013 at 11:29

Sergey Kalinichenko

729k85 gold badges1.2k silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Adding an extra condition to SQL query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related