PostgreSQL query not returning result as intended

Question

I would like to generate a list of all days where every sailor booked a boat in that particular day.

The table scheme is as follows:

CREATE TABLE SAILOR(
SID INTEGER NOT NULL,
NAME VARCHAR(50) NOT NULL,
RATING INTEGER NOT NULL,
AGE FLOAT NOT NULL,
PRIMARY KEY(SID)
);

CREATE TABLE BOAT(
BID INTEGER NOT NULL,
NAME VARCHAR(50) NOT NULL,
COLOR VARCHAR(50) NOT NULL,
PRIMARY KEY(BID)
);

CREATE TABLE RESERVE (
SID INTEGER NOT NULL REFERENCES SAILOR(SID),
BID INTEGER NOT NULL REFERENCES BOAT(BID),
DAY DATE NOT NULL,
PRIMARY KEY(SID, BID, DAY));

The data is as follows:

INSERT INTO SAILOR(SID, NAME, RATING, AGE)
VALUES
  (64, 'Horatio', 7, 35.0),
  (74, 'Horatio', 9, 35.0);

INSERT INTO BOAT(BID, NAME, COLOR)
VALUES
  (101, 'Interlake', 'blue'),
  (102, 'Interlake', 'red'),
  (103, 'Clipper', 'green'),
  (104, 'Marine', 'red');

INSERT INTO RESERVE(SID, BID, DAY)
VALUES+
  (64, 101, '09/05/98'),
  (64, 102, '09/08/98'),
  (74, 103, '09/08/98');

I have tried using this code:

SELECT DAY
FROM RESERVE R
WHERE NOT EXISTS (
SELECT SID
FROM SAILOR S
EXCEPT
SELECT S.SID
FROM SAILOR S, RESERVE R
WHERE S.SID = R.SID)
GROUP BY DAY;

but it returns a list of all days, no exception. The only day that it should return is "09/08/98". How do I solve this?

Note: PRIMARY KEY(SID, BID, DAY)); is wrong. it allows a boat to be lended multiple times on a single date(or: a person to lease multiple ships on a single day) — wildplasser
– wildplasser, Commented Jun 29, 2019 at 19:48

Tim Biegeleisen · Accepted Answer · 2019-06-29 15:32:35Z

2

I would phrase your query as:

SELECT r.DAY
FROM RESERVE r
GROUP BY r.DAY
HAVING COUNT(DISTINCT r.SID) = (SELECT COUNT(*) FROM SAILOR);

Demo

The above query says to return any day in the RESERVE table whose distinct SID sailor count matches the count of every sailor.

This assumes that SID sailor entries in the RESERVE table would only be made with sailors that actually appear in the SAILOR table. This seems reasonable, and can be enforced using primary/foreign key relationships between the two tables.

answered Jun 29, 2019 at 15:32

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

2 Comments

Gordon Linoff Over a year ago

@RafaelPintodeFreitas . . . This was the first answer by over 15 minutes. It includes an explanation and a demo. I think this is the better answer and deserves to be accepted.

Rafael Pinto de Freitas Over a year ago

Sorry to revive the dead post, but you're right, @GordonLinoff! Thank you for the answer, Tim!

cov · Accepted Answer · 2019-06-29 15:49:37Z

1

Taking a slightly different approach of just counting unique sailors per day:

SELECT day FROM (
  SELECT COUNT(DISTINCT sid), day FROM reserve GROUP BY day
) AS sailors_per_day
WHERE count = (SELECT COUNT(*) FROM sailor);

+------------+
| day        |
|------------|
| 1998-09-08 |
+------------+

answered Jun 29, 2019 at 15:49

cov

16510 bronze badges

Collectives™ on Stack Overflow

PostgreSQL query not returning result as intended

2 Answers 2

Demo

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related