Vishnu Vishnu - 2 months ago 14
MySQL Question

Subquery in mysql VIEW

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.

Answer

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);
  .
Comments