CODEWITHSUNDEEP
mysqlsubquerysql-view
Palendrone
Palendrone

Reputation: 363

MySQL Error 1349 SELECT in SubQuery Issue

Having spent quite some time getting my head around how to get the results out of a couple of table correctly I'm absolutely back to square one as the Views in MySQL won't allow me to create one with a SubQuery...

I found a similar thread that states I can write separate views for the sub query but having attempted this I have got myself in a bit of a muddle.

My original query is:

SELECT a.*
    FROM (SELECT H.username, 
    H.variety,
    IFNULL(SUM(H.weight),0) - IFNULL(HU.weight,0) As qty
        FROM harvest H
            LEFT JOIN harvest_used HU
            ON H.variety = HU.variety AND H.username = HU.username
            WHERE H.username = 'palendrone'
            GROUP BY H.variety
            ORDER BY H.variety ASC
     ) a

Now this works perfectly, but as described I cannot figure out what I need to do in order to convert it to make it run as a view on my MySQL server...

Any guidance or help would be much appreciated, Thanks.

Upvotes: 0

Views: 62

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269713

Your outer query isn't doing anything, so just remove it:

SELECT H.username, H.variety,
       COALESCE(SUM(H.weight), 0) - COALESCE(SUM(HU.weight, 0)) As qty
FROM harvest H LEFT JOIN
     harvest_used HU
     ON H.variety = HU.variety AND H.username = HU.username
WHERE H.username = 'palendrone'
GROUP BY H.variety
ORDER BY H.variety ASC;

MySQL does not allow subqueries in the from clauses for views. This is a peculiar limitation of MySQL that doesn't seem to exist in any other databases.

Upvotes: 1

Palendrone
Palendrone

Reputation: 363

Saw that the above solution had a SUM missing and also duplicated the results. Using DISTINCT seemed to fix the issue, Here's my answer:

SELECT H.username,
     H.variety,
     SUM(distinct(H.weight)) - SUM(distinct(HU.weight)) As qty
           FROM harvest H
                LEFT JOIN harvest_used HU
                ON H.variety = HU.variety AND H.username = HU.username
                WHERE H.username = 'palendrone'
                GROUP BY H.variety
                ORDER BY H.variety ASC

Upvotes: 0

Related Questions