inner join on two select queries in mysql table

Question

I have this table

id empid  reaction        date_t
1  emp090 not_interested  2017-09-16
2  emp090 not_interested  2017-09-16

I am looking to count the number of reaction as per the empid and date_t. I have tried this query

SELECT c.COUNT(reaction) as interested,c.empid FROM `cases` c 
WHERE c.reaction="interested" and c.empid="EMP12654" 
  AND c.date_t BETWEEN  "2017-09-15"  AND "2017-09-18" 
INNER JOIN 
( SELECT cases.empid COUNT(reaction) not interested FROM `cases` 
  WHERE cases.reaction="not_interested" and cases.empid="EMP12654" 
    AND cases.date_t BETWEEN  "2017-09-15"  AND "2017-09-18" ) 
AS alpha on alpha.empid=c.empid;

Can anyone tell me how to do it correctly?

COUNT(c.reaction) - or, more likely, COUNT(0). Also look again at query syntax. — Strawberry, Sep 18 '17 at 16:21
If you're still struggling, see: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Strawberry, Sep 18 '17 at 16:24
I corrected that but it still giving me error ,"you have error near inner join" — , Sep 18 '17 at 16:25

Hatim Stovewala · Answer 1 · 2017-09-18T16:28:51.123

0

Here you go.

SELECT empid, count(reaction) as count, date_t
FROM cases
WHERE reaction="interested" AND empid="EMP12654" 
AND date_t BETWEEN "2017-09-15" AND "2017-09-18" 
GROUP BY empid, date_t;

edited Sep 18 '17 at 16:28

answered Sep 18 '17 at 16:25

Hatim Stovewala

1,333
10
19

Missed it @Strawberry. I have updated it. You could have also done the same by updating my answer instead of down voting it. – Hatim Stovewala Sep 18 '17 at 16:32
That wouldn't have been as much fun. – Strawberry Sep 18 '17 at 16:36
Hahahah. Too good. Hope to see you around – Hatim Stovewala Sep 18 '17 at 16:38

score 0 · Answer 2 · answered Sep 18 '17 at 16:38

The error in your query is related to several error : wrong sequence between where an join clause, missing table name for inner join subselect, missing comma between column name in subquery, missing on clause

but if you want only the count of interested for date_t and empid

You can use group by eg:

  SELECT  
         c.empid
       , c.date_t
       , c.COUNT(*) as interested
  FROM `cases` c 
  WHERE  WHERE c.reaction="interested" 
  AND c.empid="EMP12654" 
  AND c.date_t BETWEEN  "2017-09-15"  AND "2017-09-18"
  GROUP BY  c.empid, c.date_t

inner join on two select queries in mysql table

2 Answers2