Unable to get desired output using mysql joins - table schema and test data provided

Question

we have these entities/tables -

users with fields uuid.
wallet with field userId which maps to uuid of users.
currency which are used to group coupons (to be issued to users) under a logical block.
coupon which can be issued to users, which have userId column again mapping to uuid of users. Coupon also has currencyId column.
- Issuing of coupon updates balances in wallet table.
- One user can have one wallet against each currency. wallet table has currencyId column only and does not have couponId column.
- Multiple coupons can be issued to a single currency.

We were so far fetching balances of users (with dummy mobile number 1111111111) against each currency with this query -

SELECT u.uuid  as cuuid,u.mobile,u.email,SUM(IF(c.amount IS NULL,0,c.amount)) amountIssued,
                 w.balance availableAmount ,w.expiredBalance expiredAmount,
                 w.exhaustedBalance exhaustedAmount,w.currencyId,cy.currencyName
                 FROM users u
                 LEFT JOIN coupon  c ON  u.uuid = c.userId
                 LEFT JOIN wallet w  ON w.userId = c.userId AND w.currencyId = c.currencyId
                 LEFT JOIN currency cy ON w.currencyId = cy.id
                 WHERE u.mobile = "1111111111"
                      and (c.merchantId = "3" or c.merchantId = -1)
                      GROUP BY c.currencyId;

However, above query returns only those wallets which are mapped to currencies with non zero coupons issued.

Now, we need to also show wallets with no issued coupons as non zero balance wallets. I am currently trying to do an additional query to get all wallets which are not linked with issued coupons at all and clubbing the result with that of the existing query. But I am unable to get around with the where condition, which is why I am getting null output -

SELECT u.uuid  as cuuid,u.mobile,u.email, (w.balance+w.expiredBalance+w.exhaustedBalance) as amountIssued,
                 w.balance availableAmount ,w.expiredBalance expiredAmount,
                 w.exhaustedBalance exhaustedAmount,w.currencyId,cy.currencyName
                 FROM users u
                 LEFT JOIN coupon  c ON  u.uuid = c.userId
                 LEFT JOIN wallet w  ON w.userId = c.userId AND w.currencyId = c.currencyId
                 LEFT JOIN currency cy ON w.currencyId = cy.id
                 WHERE u.mobile = "1111111111"
                      and (c.merchantId = "3" or c.merchantId = -1)
                      and c.id is null
                      GROUP BY c.currencyId;

I am expecting the wallet against currency 5 to be returned in its result.

Db schema and test data

Here are the db schema involved and some test data, for which the existing query is returning one row (as expected) and I am trying the 2nd query to return the other wallet row -

    CREATE TABLE `users` (
      `uuid` varchar(600) DEFAULT NULL,
      `mobile` varchar(60) DEFAULT NULL,
      `email` varchar(300) DEFAULT NULL,
      `addedon` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      `updatedon` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `coupon` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` varchar(200) DEFAULT NULL,
  `currencyId` int(11) NOT NULL,
  `amount` decimal(15,2) DEFAULT '0.00',
  `couponStatus` char(1) NOT NULL DEFAULT 'I',
  `issueRef` varchar(100) DEFAULT NULL,
  `merchantId` int(11) DEFAULT NULL,
  `validFrom` timestamp NULL DEFAULT NULL,
  `validTo` timestamp NULL DEFAULT NULL,
  `message` varchar(256) DEFAULT NULL,
  `addedon` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedon` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `userIdIdx` (`userId`)
) ENGINE=InnoDB AUTO_INCREMENT=10455 DEFAULT CHARSET=utf8;


CREATE TABLE `currency` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `currencyCode` varchar(30) DEFAULT NULL,
  `merchantId` int(11) NOT NULL DEFAULT '0',
  `currency` varchar(30) NOT NULL DEFAULT 'INR',
  `currencyName` varchar(100) NOT NULL,
  `validFrom` timestamp NULL DEFAULT NULL,
  `validTo` timestamp NULL DEFAULT NULL,
  `addedon` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedon` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `merchantIdIdx` (`merchantId`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;



CREATE TABLE `wallet` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` varchar(200) NOT NULL DEFAULT '',
  `balance` decimal(15,2) NOT NULL DEFAULT '0.00',
  `expiredBalance` decimal(15,2) NOT NULL DEFAULT '0.00',
  `exhaustedBalance` decimal(15,2) NOT NULL DEFAULT '0.00',
  `currencyId` int(11) NOT NULL,
  `addedon` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedon` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`Id`),
  KEY `userIdIdx` (`userId`),
  KEY `currencyIdIdx` (`currencyId`)
) ENGINE=InnoDB AUTO_INCREMENT=8901 DEFAULT CHARSET=latin1;

Insert queries -

    INSERT INTO `users` (`uuid`, `mobile`, `email`, `addedon`, `updatedon`) VALUES ('9146748363211993410', '1111111111', '1111111111@test.com', '2018-06-08 16:15:29', '2018-06-08 16:15:29');


    INSERT INTO `coupon` (`id`, `userId`, `currencyId`, `amount`, `couponStatus`, `issueRef`, `merchantId`, `validFrom`, `validTo`, `message`, `addedon`, `updatedon`) VALUES (10454, '9146748363211993410', 6, 2.00, 'I', 'certificate upgrade again 543', 3, '2018-04-19 00:00:00', '2018-07-19 23:59:59', 'priority 2 coupon', '2018-06-12 19:21:44', '2018-06-12 19:21:44');

INSERT INTO `currency` (`id`, `currencyCode`, `merchantId`, `currency`, `currencyName`, `validFrom`, `validTo`, `addedon`, `updatedon`) VALUES (5, NULL, 3, 'INR', 'new currency',  '2016-01-06 18:56:46', '2019-02-12 11:14:23', '2017-10-05 10:38:08', '2018-02-16 13:09:20');

INSERT INTO `currency` (`id`, `currencyCode`, `merchantId`, `currency`, `currencyName`, `validFrom`, `validTo`, `addedon`, `updatedon`) VALUES (6, NULL, 3, 'INR', 'new currency 2',  '2016-01-06 18:56:46', '2019-02-12 11:14:23', '2017-10-05 10:38:08', '2018-02-16 13:09:20');



INSERT INTO `wallet` (`Id`, `userId`, `balance`, `expiredBalance`, `exhaustedBalance`, `currencyId`, `addedon`, `updatedon`) VALUES (8899, '9146748363211993410', 2.00, 0.00, 0.00, 6, '2018-06-12 19:21:44', '2018-06-12 19:21:44');

INSERT INTO `wallet` (`Id`, `userId`, `balance`, `expiredBalance`, `exhaustedBalance`, `currencyId`, `addedon`, `updatedon`) VALUES (8900, '9146748363211993410', 0.00, 0.00, 0.00, 5, '2018-06-12 19:21:46', '2018-06-12 19:21:46');

Update

As per comment of @Barmer,changed join clause but still getting same result -

SELECT u.uuid  as cuuid,u.mobile,u.email, (w.balance+w.expiredBalance+w.exhaustedBalance) as amountIssued,
                 w.balance availableAmount ,w.expiredBalance expiredAmount,
                 w.exhaustedBalance exhaustedAmount,w.currencyId,cy.currencyName
                 FROM users u
                 LEFT JOIN coupon  c ON  u.uuid = c.userId and c.id is null
                 LEFT JOIN wallet w  ON w.userId = c.userId AND w.currencyId = c.currencyId
                 LEFT JOIN currency cy ON w.currencyId = cy.id
                 WHERE u.mobile = "1111111111"
                      and (c.merchantId = "3" or c.merchantId = -1)
                      GROUP BY c.currencyId;

Update 2

As suggested by @Indent, tried doing LEFT JOIN coupon c ON u.uuid = c.userId and (c.merchantId = 3 or c.merchantId = -1) to filter "coupon" before join, but this too does not seem to work. If I add the condition c.id is null condition in the join, I get a null wallet row in the result set -

SELECT w.Id, u.uuid AS cuuid,u.mobile,u.email, (w.balance+w.expiredBalance+w.exhaustedBalance) AS amountIssued,
 w.balance availableAmount,w.expiredBalance expiredAmount,
 w.exhaustedBalance exhaustedAmount,w.currencyId,cy.currencyName
FROM users u
LEFT JOIN coupon c ON u.uuid = c.userId AND (c.merchantId = "3" OR c.merchantId = -1) and c.id is null
LEFT JOIN wallet w ON w.userId = c.userId AND w.currencyId = c.currencyId
LEFT JOIN currency cy ON w.currencyId = cy.id
WHERE u.mobile = "1111111111"
GROUP BY w.Id;

Result -

"Id"    "cuuid" "mobile"    "email" "amountIssued"  "availableAmount" "expiredAmount"   "exhaustedAmount"   "currencyId"    "currencyName"

\N  "9146748363211993410"   "1111111111"    "1111111111@test.com"   \N  \N  \N  \N  \N  \N

Also, tried the following simple query -

SELECT *
FROM wallet w, users u, coupon c
WHERE u.mobile = "1111111111" AND u.uuid="9146748363211993410" 
AND w.userId = u.uuid AND c.currencyId = w.currencyId;

It is returning all combinations of wallets and coupons with currency ID=5 and 6. Wallets belonging to other users under these currency IDs are also returned. I am not able to filter out only the wallets belonging to this user. Does this point to what is fundamentally wrong here?

I feel this question is not a duplicate to the one marked here. Can others comment?

Update 3

Added sql fiddle of the problem.

If you want to get users with no coupons, you need to put the conditions on the `coupon` table in the `ON` clause of that `LEFT JOIN`, not the `WHERE` clause. Otherwise you'll filter out the no-coupon users because `c.merchangeId` is `NULL`. — Barmar, Jun 14 '18 at 05:05
And don't include non-aggregate columns in the SELECT, unless they also appear in the GROUP BY — Strawberry, Jun 14 '18 at 05:08
@Barmar, done what you have pointed out. Please check my question update. Still getting same result. — Sandeepan Nath, Jun 14 '18 at 05:12
Try this : LEFT JOIN coupon c ON u.uuid = c.userId and (c.merchantId = 3 or c.merchantId = -1) to filter "coupon" before join — Indent, Jun 14 '18 at 09:58
@Indent tried that but still did not work. Please see my update 2 section in question. — Sandeepan Nath, Jun 14 '18 at 10:54
can you create a sample with small data using http://sqlfiddle.com/ ? — Indent, Jun 14 '18 at 11:30
@Indent created. Please check http://sqlfiddle.com/#!9/eee891/4. You can see that the a null wallet is returned after taking your changes. — Sandeepan Nath, Jun 14 '18 at 13:22
http://sqlfiddle.com/#!9/dd9947/8 : I have removed "and c.id is null" in join part; In the join you need filter the "good" lines — Indent, Jun 15 '18 at 06:22

Unable to get desired output using mysql joins - table schema and test data provided

0 Answers0

Linked