CODEWITHSUNDEEP
sqliteselect
OleGG
OleGG

Reputation: 8657

SQLite "in" expression on multiple columns

I have such database query, which works well in MySQL:

select authors.name, authors.surname, books.name, publishers.name, copies.comment 
from authors, books, publishers, copies
where (authors.id, books.id) in (select authorship.author_id, authorship.book_id from authorship)   
and (books.id, publishers.id, copies.publish_id) in (select publishment.book_id, publishment.publisher_id, publishment.id from publishment);

In SQLite database I've got such error on part "where (authors.id, books.id) in":

Error: near ",": syntax error

Is it possible to make such type of query in SQLite (maybe - with different syntax)?

PS: I know that using joins is better for those case, but I want to know if it is possible for general knowledge.

Upvotes: 0

Views: 608

Answers (1)

Tomalak
Tomalak

Reputation: 338308

I know that using joins is better for those case, but I want to know if it is possible for general knowledge.

No, this is not possible. Use explicit joins.

SELECT
  a.name, a.surname, b.name, p.name, c.comment 
FROM
  authors                a
  INNER JOIN authorship ab ON ab.author_id = a.id
  INNER JOIN books       b ON b.id         = ab.book_id
  INNER JOIN publishment p ON p.book_id    = b.book_id
  INNER JOIN copies      c ON c.publish_id = p.id

(And by the way, you should do the same for mySQL)

Upvotes: 1

Related Questions