CODEWITHSUNDEEP
sqlcountwhere-clause
GenericUser
GenericUser

Reputation: 229

SQL count used in where clause

I'm kind of new to sql. I have got a question involving using the function count() as restriction the the where-clause in my sql.

tbl_meeting

ID
TIMESTAMP
CAPACITY
...

tbl_attendance

ID
NAME
...

(I've also got a table between because multiple attendance can go to multiple meetings)

I want to select all the meeting that are full. It should look something like this but i can figure it out.

select *
, count(LAM.id) as amount_attending
from tbl_meetings M
left join tbl_lnk_attendance_meeting LAM
on M.id = LAM.meeting_id
where M.capacity <> amount_attending

Thanks.

Upvotes: 1

Views: 81

Answers (3)

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

Use this approach to find full attendance of meeting

SELECT *
FROM tbl_meetings M
WHERE M.capacity = (SELECT COUNT(LAM.id) 
                    FROM tbl_lnk_attendance_meeting LAM 
                    WHERE M.id = LAM.meeting_id)

Upvotes: 1

Kelewan
Kelewan

Reputation: 33

You're already carrying out the count function in your select statement so your where clause just needs a comparison to a number.

select *
, count(LAM.id) as amount_attending
from tbl_meetings M
left join tbl_lnk_attendance_meeting LAM
on M.id = LAM.meeting_id
where amount_attending = (whatever number constitutes a full meeting)

Upvotes: 0

tapos ghosh
tapos ghosh

Reputation: 2202

Try this:

select * , count(LAM.id) as amount_attending
from tbl_meetings M
left join tbl_lnk_attendance_meeting LAM
on M.id = LAM.meeting_id 
group by LAM.id

Upvotes: 0

Related Questions