CODEWITHSUNDEEP
sqlpostgresql
bux
bux

Reputation: 7739

Where clause on joined table

For the following tables:

ROOM

+----+--------+
| ID |  NAME  |
+----+--------+
|  1 | ROOM_1 |
|  2 | ROOM_2 |
+----+--------+

ROOM_STATE

+----+---------+------+------------------------+
| ID | ROOM_ID | OPEN |          DATE          |
+----+---------+------+------------------------+
|  1 |       1 |    1 |    2000-01-01 00:00:00 |
|  2 |       2 |    1 |    2000-01-01 00:00:00 |
|  3 |       2 |    0 |    2000-01-06 00:00:00 |
+----+---------+------+------------------------+

Stored data is room with last changed state:

ROOM_1 is still open, ROOM_2 is closed (no opened since 2000-01-06). How to select actual opened rooms names with a join ? If i wrote:

SELECT ROOM.NAME 
FROM ROOM
  INNER JOIN ROOM_STATE ON ROOM.ID = ROOM_STATE.ROOM_ID
WHERE ROOM_STATE.OPEN = 1

ROOM_1 and ROOM_2 are selected because ROOM_STATE with ID 2 is OPEN.

SQL Fiddle: http://sqlfiddle.com/#!9/68e8bf/3/0

Upvotes: 3

Views: 129

Answers (5)

bux
bux

Reputation: 7739

Possible solution where join condition is on wanted ROOM_STATE row:

SELECT R.ID, R.NAME 
FROM ROOM AS R
INNER JOIN ROOM_STATE AS RS ON R.ID = RS.ROOM_ID 
  AND RS.DATE = (
    SELECT DATE 
    FROM ROOM_STATE
    WHERE ROOM_ID = R.ID
    ORDER BY DATE DESC 
    LIMIT 1
  )
 WHERE RS.OPEN = 1

Upvotes: 0

Tom H
Tom H

Reputation: 47444

There are several ways to skin this cat. @GiorgosBetsos' answer is one way. Another:

WITH Numbered_Status AS
(
    SELECT
        id,
        room_id,
        open,
        ROW_NUMBER() OVER (PARTITION BY room_id ORDER BY date DESC) AS row_num
    FROM
        Room_State
)
SELECT
    R.id,
    R.name,
    S.open
FROM
    Numbered_Status S
INNER JOIN Room R ON R.room_id = S.room_id
WHERE
    row_num = 1 AND
    open = 1

You could also use NOT EXISTS:

SELECT
    R.id,
    R.name,
    S.open
FROM
    Room_State S
INNER JOIN Room R ON R.id = S.room_id
WHERE
    S.open = 1 AND
    NOT EXISTS
    (
        SELECT *
        FROM Room_State S2
        WHERE
            S2.room_id = S.room_id AND
        S2.date > S.date
    )

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can use the following query:

SELECT R.ID, R.NAME 
FROM ROOM AS R
INNER JOIN ROOM_STATE AS RS ON R.ID = RS.ROOM_ID AND RS.OPEN = 1
LEFT JOIN ROOM_STATE AS RS2 ON R.ID = RS2.ROOM_ID AND RS2.OPEN = 0 AND RS2.DATE > RS.date
WHERE RS2.ID IS NULL

Demo here

This will return all rooms that are related to an 'open' state and have no relation to a 'closed' state that has a date posterior to the date of the 'open' state.

Upvotes: 2

Olli
Olli

Reputation: 729

If you can guarantee that for each open you have a close and this is always the case, you could also use this crazy sql ;)

SELECT r.ID, r.NAME 
FROM ROOM r 
WHERE (select sum(CASE WHEN OPEN = 0 THEN -1 ELSE 1 END) from room_state rs where rs.room_id=r.id) = 1;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

In Postgres, I would recommend distinct on:

select distinct on (rs.room_id) r.name, rs.*
from room_state rs join
     room r
     on rs.room_id = r.id
order by rs.room_id, rs.date desc;

distinct on is specific to Postgres. It guarantees that the results have only one row for each room (which is what you want). The chosen row is the first row encountered, so this chooses the row with the largest date.

Another fun method is to use a lateral join:

select r.*, rs.*
from room r left join lateral
     (select rs.*
      from room_state rs
      where rs.room_id = r.id
      order by rs.date desc
      fetch first 1 row only
     ) rs;

Upvotes: 3

Related Questions