CODEWITHSUNDEEP
sqlpostgresql
Rafael Pinto de Freitas
Rafael Pinto de Freitas

Reputation: 3

PostgreSQL query not returning result as intended

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?

Upvotes: 0

Views: 68

Answers (2)

cov
cov

Reputation: 165

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 |
+------------+

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520878

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.

Upvotes: 2

Related Questions