I can't inner join with a table twice

I have these two tables:

CREATE TABLE users(
  id,
  username,
  pass
)

CREATE TABLE articles(
  id,
  ownerId,
  authorId,
  CONSTRAINT fk_articles_owner FOREIGN KEY(ownerId) REFERENCES users(id),
  CONSTRAINT fk_articles_author FOREIGN KEY(authorId) REFERENCES users(id)
)

CREATE TABLE users(
  id,
  username,
  pass
)

CREATE TABLE articles(
  id,
  ownerId,
  authorId,
  CONSTRAINT fk_articles_owner FOREIGN KEY(ownerId) REFERENCES users(id),
  CONSTRAINT fk_articles_author FOREIGN KEY(authorId) REFERENCES users(id)
)

I need to inner join the two tables twice to get the owner and the author in one request. the query I write is:

select * from articles
INNER JOIN users ON articles.ownerId =users.id
INNER JOIN users ON articles.authorId=users.id

select * from articles
INNER JOIN users ON articles.ownerId =users.id
INNER JOIN users ON articles.authorId=users.id

and I get this error: sqlMessage: "Not unique table/alias: 'users'" Is there a way to do it without having to query two times? I suspect that the error is because the users table would output duplicate columns but how do I rename those columns for each inner join?

4 Replies

circles•3y ago

Why are you joining the same table twice? Did you mean to join the users table based on two conditions? If that's the case, then you could do something like this:

select * from articles
INNER JOIN users ON articles.ownerId = users.id OR articles.authorId = users.id

select * from articles
INNER JOIN users ON articles.ownerId = users.id OR articles.authorId = users.id

This will return the articles by the user, no matter if he's the author or the owner of it

venegoOP•3y ago

no I want to get the name of the owner and the name of the author. that's why I need to do this. yes but I need to get both It would have been easier if I just hard coded the names in the articles table but If the usernames got changed the constraint would need to cascade etc...

circles•3y ago

Try something like this:

select a.*, owner, author from articles a
INNER JOIN (SELECT username FROM users WHERE users.username = a.ownerId) as owner
INNER JOIN (SELECT username FROM users WHERE users.username = a.authorId) as author

select a.*, owner, author from articles a
INNER JOIN (SELECT username FROM users WHERE users.username = a.ownerId) as owner
INNER JOIN (SELECT username FROM users WHERE users.username = a.authorId) as author

I'm not sure if it's gonna work, I wrote it from top of my head, but try to get the idea.

venegoOP•3y ago

kinda... it only needed the two(same) tables to have unique tables alias and column aliases. it worked however this way:

select a.*, u.username as owner, uu.username as author
from articles as a
inner join users u on a.owner=u.id
inner join users uu on a.author=uu.id

select a.*, u.username as owner, uu.username as author
from articles as a
inner join users u on a.owner=u.id
inner join users uu on a.author=uu.id

thanks tho @circles

Gaming

Programming

I can't inner join with a table twice