Subquery in mysql VIEW

Question

The following SQL Query, when used to create a VIEW is showing the usage of subquery error. This is to fetch the values of a field named CODE from table1 where those values are not present in table2, and also when the Status of those CODE in table2 is not "ASSIGNED" or "SOLD".

SELECT S1.CODE
FROM STOCK S1
LEFT JOIN (  
        SELECT t1.CODE
        FROM STOCK t1
        JOIN SALES t2 ON t2.CODE = t1.CODE
        WHERE t2.STATUS IN ('Sold', 'Assigned')) S2 ON S2.CODE = S1.CODE
WHERE S2.CODE IS NULL

The SQL query is providing good output, but cannot use this while creating VIEW. Please help me solve this.

View doesnot allow sub queries. You can create another view and the query from it. — Sougata Bose, Sep 10 '16 at 04:24
You can see the question [link]http://stackoverflow.com/questions/8428641/views-select-contains-a-subquery-in-the-from-clause — Shailesh Chauhan, Sep 10 '16 at 04:51

Rich Bianco · Accepted Answer · 2016-09-10T06:03:30.400

Good information about MySQL Subqueries

The reason your query wouldn't work is that you cannot have a subquery in the FROM clause in MySql view.

I couldn't understand your requirement with 100% certainty but it seemed you wanted rows from one table that didn't exist in another or they could exist in another but they couldn't have a certain status. If my understanding of the requirement was right than this query does the same thing. These were the tables I had to work with right now verified this works fine.

If I'm wrong on the requirement please provide guidance.

/* 
   VIEW: v_testview
   get rows where adminid not in second table
   and rows that are in second table but have
   an email not equal duckduckgo@notgoogle.com    
*/
CREATE VIEW v_testview AS  
  SELECT s.adminid, s.company
  FROM panel_customers s
  WHERE s.adminid NOT IN 
               (SELECT sa.adminid 
                FROM panel_admins sa 
                WHERE sa.email = 'duckduckgo@notgoogle.com')
  AND s.adminid NOT IN 
               (SELECT sa.adminid 
                FROM panel_admins sa);
  .

Thanks.. It worked with some modifications in the condition part. — Vishnu, Sep 10 '16 at 06:51

Subquery in mysql VIEW

1 Answers1