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".
FROM STOCK S1
LEFT JOIN (
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
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 firstname.lastname@example.org */ 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 = 'email@example.com') AND s.adminid NOT IN (SELECT sa.adminid FROM panel_admins sa); .