CODEWITHSUNDEEP
mysqldatabasejoin
H.L.
H.L.

Reputation: 75

mysql count rows from one table using group and join from 2nd table

I need a some guidance. I have found a flaw in my coding.

I have 2 tables:

table 1: eventinfo

id
evid
eventtype
division
evtdate
tod

Plus more fields, but not important for this

SQL query: 

SELECT evid, eventype, evtdate , tod 
FROM `eventinfo` 
WHERE evid =  105 



eventtype     division  evtdate tod
beginner        0   2018-02-17  AM
intermediate    1   2018-02-17  AM
intermediate    2   2018-02-17  AM
advanced        1   2018-02-17  AM
advanced        2   2018-02-17  AM
beginner        0   2018-02-18  AM
intermediate    1   2018-02-18  AM
intermediate    2   2018-02-18  AM
advanced        1   2018-02-18  AM
advanced        2   2018-02-18  AM

table 2: entries

id
entid
firstname
lastname
eventcat
evtdate
division
tod

plus more rows, but not important for this.

SQL query: 

SELECT evid, eventcat, evtdate , tod , COUNT( * ) AS count 
FROM entries 
WHERE evtid= '105' and  evtstatus= 'pending' 
GROUP BY evtdate, tod , evntcat


eventcat        evtdate     tod count
Advanced        2018-02-17  AM  35
Intermediate    2018-02-17  AM  18
Beginner        2018-02-17  AM  4
Advanced        2018-02-18  AM  35
Intermediate    2018-02-18  AM  18

On the page I need, I need to count entries into this course/event to see how many more seats are available. (this way I know if I need to "tell people they can't enter" I need the grouping, because the division doesn't matter at this point of the coding.

OK, here's my problem. The above query works great, EXCEPT my flaw is where there's no entries for the course/event yet. I need the above to show that the 2nd date beginner is 0. (which i understand my current query will not show)

eventcat        evtdate     tod count
Advanced        2018-02-17  AM  35
Intermediate    2018-02-17  AM  18
Beginner        2018-02-17  AM  4
Advanced        2018-02-18  AM  35
Intermediate    2018-02-18  AM  18
**Beginner        2018-02-18  AM  0** <-----

I've tried some some joins the tables, but with no luck. (odd results and the database says I need to SET SQL_BIG_SELECTS=1)

Any help would be greatly appreciative.

Upvotes: 1

Views: 152

Answers (2)

user9152967
user9152967

Reputation:

Use LEFT JOIN:

 SELECT i.eventtype, i.evtdate , i.tod , COUNT( * ) AS count 
FROM eventinfo AS i
LEFT JOIN entries as e On e.eventcat = i.eventtype
GROUP BY i.eventtype, i.evtdate , i.tod ;

demo

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271023

If I understand correctly, one method uses a correlated subquery:

select ei.*,
       (select count(*)
        from entries e
        where e.evtid = ei.evtid and e.evtstatus = 'pending'
       ) as cnt
from eventinfor ei

Upvotes: 0

Related Questions