SQLite "in" expression on multiple columns

Question

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.

Tomalak · Accepted Answer · 2012-01-25 11:39:59Z

1

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)

edited Jan 25, 2012 at 11:39

answered Jan 25, 2012 at 11:34

Tomalak

339k68 gold badges547 silver badges635 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQLite "in" expression on multiple columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related