CODEWITHSUNDEEP
mysql
Jacky Lau
Jacky Lau

Reputation: 37

Trouble in MySQL querying

I am currently developing a digital mobile loyalty app and I have face a problem in querying mysql code...

I have 4 tables: Customer, Card, Redemption, Merchant

Currently what I am trying to do is that I want my system to first check if the customer possess the loyalty card of a certain merchant when they are making a redemption

The redemption code will be generated by the merchant...

The problem is how should I query my code to do so?

Customer

 - Customer_ID
 - C_Email
 - C_Password
 - C_Name

Card

 - Card_ID
 - Chop_Amt
 - Customer_ID*
 - Merchant_ID*

Merchant

 - Merchant_ID
 - Merchant_Name

Redemption

 - Red_ID
 - Red_Code
 - Card_ID*
 - Merchant_ID*

I tried writing a code my own just now...can some1 please help me check?

select * 
from customer customer 
join card card 
  on customer.customer_id = card.customer_id 
join redemption redemption 
  on card.merchant_id = redemption.merchant_id 
where card.merchant_id = redemption.merchant_id and
      redemption.red_code = 'A002'

Upvotes: 1

Views: 77

Answers (1)

Bryan
Bryan

Reputation: 6752

I would recommend putting a unique index on the Card table for merchant / customer entries. IF you go with this approach, you can do an INSERT INTO ... ON DUPLICATE KEY UPDATE id = id. This will ensure that a record exists regardless.

To create the index, do this

CREATE UNIQUE INDEX customer_merchant ON Card (Customer_ID, Merchant_ID);

Now that you have that in place, go ahead and do the insert, which will NOT insert a new row if one already exists

INSERT INTO Card (Chop_Amt, Customer_ID, Merchant_ID) VALUES(0.00, 1, 1) ON DUPLICATE KEY UPDATE Card_ID = Card_ID

The UPDATE Card_ID = Card_ID part is simply an alias to "don't do anything", as it's setting the Card_ID to itself

Upvotes: 1

Related Questions