带有userId的2表

Question

CREATE TABLE event (
   id SERIAL PRIMARY KEY,
   city VARCHAR ( 30 ),
   author_id INT REFERENCES users (id) NOT NULL,
   text VARCHAR,
   cDate TIMESTAMPTZ NOT NULL DEFAULT NOW(),
   uDate TIMESTAMPTZ NOT NULL DEFAULT NOW()
);


CREATE TYPE InviteStatus AS ENUM ('pending', 'approved', 'declined');
CREATE TABLE invite  (
   sender_id INT REFERENCES users (id) NOT NULL,
   receiver_id INT REFERENCES users (id) NOT NULL,
   event_id INT REFERENCES event (id) NOT NULL,
   receiver_approved InviteStatus DEFAULT 'pending' NOT NULL,
   cDate TIMESTAMPTZ NOT NULL DEFAULT NOW(),
   uDate TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

我们有两张桌子。我们得到userId，也就是sender_id。我们需要从事件获取所有列，并在结果中从invite创建字段receiver_approved，如果在invite表和event.id = invite.event_id中存在userId = sender_id作为外部参数

Answer 1

SELECT e.*, i.receiver_approved
FROM invite i
JOIN event e on e.id = i.event_id
WHERE i.sender_id = :userID

其中userID是外部的一个参数