CODEWITHSUNDEEP
sqlite
Chromium
Chromium

Reputation: 517

union results with the same conditions

I have a inventory table in my sqlite database which contains columns category, name, and count. I want to filter out the rows with count less than 2, and put another category row before all the other data in the with the category so that it's easier to store them in tree views in GUIs.

My data in the table is:

category    name        count     
----------  ----------  ----------
fruit       apple       4         
fruit       banana      3         
fruit       peach       2         
meat        pork        1         
meat        beef        1         
vegetable   spinach     5         
vegetable   carrot      2

and what I do is:

SELECT category, '', 0 FROM inventory
UNION
SELECT category, name, count FROM inventory
    WHERE category IN
        (SELECT category FROM inventory
         UNION
         SELECT category FROM inventory)
    AND count > 1;

and what I got is:

category    ''          0         
----------  ----------  ----------
fruit                   0         
fruit       apple       4         
fruit       banana      3         
fruit       peach       2         
meat                    0         
vegetable               0         
vegetable   carrot      2         
vegetable   spinach     5

And actually what I want in this case is without the meat row.

I think the problem is that the WHERE clause only applies to the SELECT after the first UNION. But how can I also put the condition on the first SELECT? Can anyone help me? Thanks in advance!

Upvotes: 1

Views: 87

Answers (1)

CL.
CL.

Reputation: 180162

Compound queries are built from complete queries (except for ORDER BY).

You should use UNION without ALL only if you actually want to remove duplicates.

You need ORDER BY to sort the header rows into the correct position.

SELECT category, NULL AS name, NULL AS count
FROM inventory
GROUP BY category
HAVING max(count) > 1

UNION ALL

SELECT category, name, count
FROM inventory
WHERE count > 1

ORDER BY category, name;

Upvotes: 1

Related Questions