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-16 09:50:56Z

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, 2013 at 9:50

answered Mar 16, 2013 at 7:15

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Mark Martin Over a year ago

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....

Mahmoud Gamal Over a year ago

@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 Over a year ago

@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. What do you think?

Mark Martin Over a year ago

there is no records for status=1 but still for some records it returns used code as 1...

Mahmoud Gamal Over a year ago

@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 explaining this issue?

|

Collectives™ on Stack Overflow

MYSQL Inner join with select statement

1 Answer 1

SQL Fiddle Demo

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related