Show grid result from one table based on amount of same id records in another table

Applications Grid / Reports
#1

hi there,

I have a database where users can sign in for a workshop.
the workshop values are in the table .workshop.
the signed in users are in the table .signed_users.

.workshop.
{wk_id}
{wk_title}
{wk_max_users}

.signed_users.
{user_id}
{wk_id}

so in a grid i can see how many users are signed in by adding a field and some code
The question
The company want’s a treshold of a minimum of 6 users for a workshop and a list of workshops in a grid that shows only those workshops that have 6 or less users signed in.

it makes me crazy to get this done. you can’t filter or sort on an added field with the values in it and i can’t figure out a WHERE clausule or something else to filter only those records in a grid.

any help or suggestions

#2

You should be able to do a SELECT of SELECT

ex. SELECT field1, field2, field3 FROM (SELECT COUNT(*) as field1, SUM(abc) as field 2, field3 FROM table WHERE whatever = something) WHERE field2 < 6 ORDER BY field1

#3

thank you for the push in the right direction. i’m having a smal problem now because it doesn’t show the workshops that aren’t occupied (the have 0 participant) so it show the worshops that have one or more participant but not the workshops that have zero (0)

here is the code

SELECT
   ILP_rooster_deelnemer.wk_id,
   ILP_rooster.wk_titel,
   COUNT(*)
FROM
   ILP_rooster 
INNER JOIN ILP_rooster_deelnemer ON ILP_rooster.wk_id = ILP_rooster_deelnemer.wk_id
WHERE ILP_rooster_deelnemer.ev_id = 73
GROUP BY
ILP_rooster_deelnemer.wk_id
HAVING
	COUNT(*) < 6
ORDER BY
	COUNT(*) ASC
#4

You need to force the select of a workshop to have a 0 value for COUNT(*)

Try this instead

SELECT
R.wk_id,
R.wk_titel,
(SELECT COUNT(*) FROM ILP_rooster_deelnemer RD WHERE R.wk_id = RD.wk_id ) AS qty
FROM ILP_rooster R
WHERE R.ev_id = 73
HAVING qty < 6
ORDER BY qty ASC

1 Like
#5

Yes that did the trick. i never use Aliases but in this case its better because it’s a lot better to intrepid.

thanks again for the great solution. i adjusted some things and post it for anyone to see.

SELECT
R.wk_id,
R.wk_titel,
R.wk_datum_gesprek,
R.wk_tijd_s_gesprek,
R.wk_tijd_e_gesprek,
R.wk_conf_container,
R.wk_st_id,
R.wk_st_id_2,
R.wk_max_d,

(SELECT COUNT(*) FROM ILP_rooster_deelnemer RD WHERE R.wk_id = RD.wk_id ) AS INSCHRIJVINGEN
FROM ILP_rooster R
WHERE R.ev_id = [ev_id] AND R.ronde_id > 0 AND R.wk_conf_container = 0
HAVING INSCHRIJVINGEN <= [MIN_D]
ORDER BY INSCHRIJVINGEN ASC
1 Like