Basic SQL Query Assistance

Question

Can anyone please help me write this without using IN ?

SELECT firstname, lastname
FROM author
WHERE authorid IN (
SELECT authorid
FROM published_by JOIN written_by
ON written_by.bookdescid = published_by.bookdescid
WHERE  publisherid IN (3,746)
AND NOT written_by.role LIKE ('%translator%')
AND NOT published_by.role LIKE ('%editor%')
);

Struggling a bit here for a new SQL without using IN

Many thanks

Answer 1

Something like:

SELECT DISTINCT a.firstname, a.lastname
FROM author a
INNER JOIN published_by p
  ON a.authorid = p.authorid
INNER JOIN written_by w
  ON w.bookdescid = p.bookdescid
WHERE publisherid IN (3,746)
  AND NOT w.role LIKE ('%translator%')
  AND NOT p.role LIKE ('%editor%')

Although not sure why you want to avoid it, personally I think original query is clearer

Answer 2

WHERE  publisherid IN (3,746)

is the same as

WHERE  (publisherid = 3 OR publisherid = 746)

Oops, I just saw there is another IN clause in your query :-)

IN is perfect in this context. You can use EXISTS instead, if you like that better (I don't):

SELECT firstname, lastname
FROM author a
WHERE EXISTS
(
  SELECT NULL
  FROM published_by p
  JOIN written_by  w ON w.bookdescid = p.bookdescid
  WHERE p.publisherid IN (3,746)
  AND NOT w.role LIKE ('%translator%')
  AND NOT p.role LIKE ('%editor%')
  AND w.authorid = a.authorid
);