Re: DELETE and JOIN

On Mon, Mar 13, 2017 at 9:39 AM, Alexander Farber <
alexander(dot)farber(at)gmail(dot)com> wrote:

> Good evening,
>
> In a 9.5 database I would like players to rate each other and save the
> reviews in the table:
>
> CREATE TABLE words_reviews (
> uid integer NOT NULL CHECK (uid <> author) REFERENCES words_users
> ON DELETE CASCADE,
> author integer NOT NULL REFERENCES words_users(uid) ON DELETE
> CASCADE,
> nice integer NOT NULL CHECK (nice = 0 OR nice = 1),
> review varchar(255),
> updated timestamptz NOT NULL,
> PRIMARY KEY(uid, author)
> );
>
> while user names and IP addresses are saved in the other database:
>
> CREATE TABLE words_users (
> uid SERIAL PRIMARY KEY,
> ip inet NOT NULL,
> ..........
> );​
>

> ​[...]
> all previous reviews coming from the same IP in the past 24 hours:
>

​SELECT (uid, author) -- locate reviews
FROM word_reviews
JOIN words_users USING (u_id)
WHERE u_id IN ( -- from each of the following users...
SELECT wu.u_id
FROM words_users wu
WHERE wu.ip = (SELECT wui.ip FROM words_users wui WHERE wui,uid = in_uid)
-- find all users sharing the ip address of this supplied user
)​
AND updated >= [...] -- but only within the specified time period

David J.