"in" is not valid at this position

Question

i am not sure whats the problem with mysql statement. The error says " IN is not valid at this position , expecting : EOF, ';'

Here is my sql statement:

 CREATE VIEW collab AS
 SELECT a.author_name AS Author, ca.no0fCollab
 FROM (SELECT * FROM author) as a, (SELECT * FROM coauthors) as ca
     WHERE a.author_id = ca.author_id
 AND a.author_id IN (
  SELECT UA.author_id
  FROM uniq_authored UA JOING publication p 
  ON P.pubid = UA.pubid
  WHERE (p.pubkey LIKE 'journals/%' OR p.pubkey LIKE 'conf/%')
  AND p.title LIKE '%data%'
  );

Really - the error I get is ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause — P.Salmon, Oct 05 '19 at 13:51
Don't use sub queries in from correct syntax is from author a join coauthors ca on a.author_id = ca.author_id — P.Salmon, Oct 05 '19 at 13:53
Do not vandalise your posts please; people have put effort into trying to help you and you can't just invalidate their work by removing your post content. — Martijn Pieters, Oct 22 '19 at 14:34

score 0 · Answer 1 · answered Oct 05 '19 at 13:55

You can't use FROM( subquery) in view
but you don't need ....you could refactor your query using join and avoiding subquery

  CREATE VIEW collab AS
  select a.author_name Author, ca.no0fCollab
  FROM author a
  INNER JOIN coauthors ca ON a.author_id = ca.author_id
  INNER JOIN uniq_authored ua ON a.author_id = ua.author_id
  INNER JOIN publication p ON P.pubid = ua.pubid 
    AND (p.pubkey LIKE 'journals/%' OR p.pubkey LIKE 'conf/%')
      AND p.title LIKE '%data%'

"in" is not valid at this position

1 Answers1