CODEWITHSUNDEEP
sqlpostgresql
Said Savci
Said Savci

Reputation: 858

Count within the result set of a subquery

I have the following relations in my database:

Invoice                           InvoiceMeal
---------------------             ---------------------------
| InvoiceId | Total |             | Id | InvoiceId | MealId |
---------------------             ---------------------------
| 1         | 22.32 |             | 1  | 1         | 3      |
---------------------             ---------------------------
| 2         | 12.18 |             | 2  | 1         | 2      |
---------------------             ---------------------------
| 3         | 27.76 |             | 3  | 2         | 2      |
---------------------             ---------------------------

Meal                                            Type
-----------------------------------             -------------------
| Id     | Name          | TypeId |             | Id | Name       |
-----------------------------------             -------------------
| 1      | Hamburger     | 1      |             | 1  | Meat       |
-----------------------------------             -------------------
| 2      | Soja Beans    | 2      |             | 2  | Vegetarian |
-----------------------------------             -------------------
| 3      | Chicken       | 2      |
-----------------------------------

What I want to query from the database is InvoiceId and Total of all Invoices which consist of at least two Meals where at least one of the Meals is of Type Vegetarian. I have the following SQL query and it works:

SELECT 
   i."Id", i."Total"
FROM 
   public."Invoice" i
WHERE
   (SELECT COUNT(*)
   FROM public."InvoiceMeal" im
   WHERE im."InvoiceId" = i."Id" AND
                              (SELECT COUNT(*) 
                              FROM public."Meal" m, public."Type" t
                              WHERE im."MealId" = m."Id" AND
                              m."TypeId" = t."Id" AND 
                              g."Name" = 'Vegetarian') > 0
   ) >= 2;

My problem with this query is that I can not easily modify the condition that there must at least one vegetarien Meal. I want to be able, for example, to change it to at least two vegetarian meals. How can I achieve this with my query?

Upvotes: 1

Views: 79

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

I would approach this by joining the tables together and using aggregation. The having clause can handle the conditions:

select i.Id, i.Total
from InvoiceMeal im join
     Invoice i
     on i.InvoiceId = im.InvoiceId join
     Meal m
     on im.mealid = m.mealid join
     Type t
     on m.typeid = t.typeid
group by i.Id, i.Total
having count(distinct im.mealid) >= 2 and
       sum(case when t.name = 'Vegetarian' then 1 else 0 end) > 0;

I also see no reason to put double quotes around column names. That just makes the query harder to write and read.

Upvotes: 3

Related Questions