I have two tables related through another (many-to-many)
This is a schema excerpt:
CREATE TABLE user (
user_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
email TEXT NOT NULL UNIQUE
);
CREATE TABLE alias (
alias_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
address TEXT NOT NULL UNIQUE
);
CREATE TABLE alias_member (
alias_id INTEGER NOT NULL,
user_id INTEGER NOT NULL,
PRIMARY KEY(alias_id, user_id),
FOREIGN KEY(alias_id) REFERENCES alias(alias_id) ON DELETE CASCADE,
FOREIGN KEY(user_id) REFERENCES user(user_id) ON DELETE CASCADE
);
I have this query that satisfies my needs:
SELECT email
FROM
user,
alias,
alias_member
WHERE
user.user_id = alias_member.user_id
AND alias.alias_id = alias_member.alias_id
AND alias.address = 'foo@domain.tld';
A friend suggested me another query that works too but I don't get it:
SELECT
email
FROM
user
INNER JOIN
(alias INNER JOIN alias_member ON alias.alias_id = alias_member.alias_id) ON user.user_id = alias_member.user_id
WHERE address='foo@domain.tld';
Would anyone explain it for me?