MYSQL Inner join with select statement

Question

Right now I have the following query:

SELECT     
  rm.reward_name,
  rm.rewardid,
  rc.reward_code,
  rc.status,
  rc.rewardid,
  rc.add_date,
  rc.status 
from rewards_codes as rc 
INNER JOIN reward_mast as rm on rc.rewardid on rm.rewardid 
where DATE(rc.add_date) between '2012-03-16' AND '2013-03-16';

I want to fetch total no of codes,available codes from all codes,used codes

i have taken status field in rewards_codes field for differentiate code status

0 - Available to use

1- Used code

So my final output should be like following:

-----------------------------------------------------------
Reward Name     Total Codes      Available code   Used code        

my_reward       100                  40               60
extra_reward    100                  90               10
-----------------------------------------------------------

[Update]

Here is some sample data from both table...

reward_mast

rewardid        rewrd_name
1               my_reward
2               extra_reward
3               test_reward

rewards_codes

codeId  rewardid    reward_code     add_date    status
1       1           aka454          2012-11-21  0
2       2           ala499          2012-04-21  0
3       1           pao789          2012-08-21  0
4       3           zlk753          2012-01-21  0
5       2           qra954          2012-05-21  0

Mahmoud Gamal · Accepted Answer · 2013-03-16T09:50:56.667

1

Try this:

SELECT 
  rm.rewardid,
  rm.reward_name,
  IFNULL(COUNT(rc.reward_code), 0) AS 'Total Codes',
  IFNULL(SUM(rc.status = 0), 0)    AS 'Available code',
  IFNULL(SUM(rc.status = 1), 0)    AS 'Used Codes'
FROM reward_mast as rm
LEFT JOIN rewards_codes as rc on rc.rewardid = rm.rewardid 
WHERE DATE(rc.add_date) between '2012-03-16' AND '2013-03-16'
GROUP BY rm.reward_name,
         rm.rewardid;

This will give you the count of each category of status codes individually, Totalcodes, Available Codes and Used Codes.

SQL Fiddle Demo

This will give you:

| REWARDID |  REWARD_NAME | TOTAL CODES | AVAILABLE CODE | USED CODES |
-----------------------------------------------------------------------
|        1 |    my_reward |           2 |              2 |          0 |
|        2 | extra_reward |           2 |              2 |          0 |

edited Mar 16 '13 at 09:50

answered Mar 16 '13 at 07:15

Mahmoud Gamal

78,257
17
139
164

thanks for answer , i have checked this it gives proper OP for available & total code, i have checked used code for individual reward there is no single code used for reward still it show 1 used code.... – Mark Martin Mar 16 '13 at 07:29
@MarkMartin - OK, Sorry, use `LEFT JOIN` See my edit. Can you please add some sample data from both the two tables `rewards_codes` and `reward_mast` it will be very helpful to explain the problem. – Mahmoud Gamal Mar 16 '13 at 07:32
@MarkMartin - OK, and I am sorry too for late. See my edit I fixed my query, it should work fine, note that it won't give you all the names because of the where clause remove that condition and it will give you all the rows. [**like in this demo**](http://sqlfiddle.com/#!2/7ea1a/4). What do you think? – Mahmoud Gamal Mar 16 '13 at 09:52
there is no records for status=1 but still for some records it returns used code as 1... – Mark Martin Mar 16 '13 at 10:09
@MarkMartin - It should work fine, it might be another problem else where. And it is working fine in the demo. In the demo there are no `Used Codes` it is 0, it is working fine. Can you please post the exact query you tried? Also try to edit this [**demo**](http://sqlfiddle.com/#!2/7ea1a/4) explaining this issue? – Mahmoud Gamal Mar 16 '13 at 10:11
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/26287/discussion-between-mark-martin-and-mahmoud-gamal) – Mark Martin Mar 16 '13 at 10:29

MYSQL Inner join with select statement

1 Answers1

SQL Fiddle Demo