CODEWITHSUNDEEP
mysql
user3260112
user3260112

Reputation: 57

selecting data from two tables,mysql

My database is having two tables customer and transaction.

customer table is having 8 columns,transaction table is having 4 columns,

customer table primary key is id,and in transaction table there is no primary key,per customer in transaction table there exists more than one record.

I need to select 4 columns from customer ,3 columns from transaction.

here is query:

query ='SELECT c.customer_id AS "Customer ID",
               c.father_name AS "Father Name",
               c.address AS Address,
               c.phone_number AS "Phone Number",
               c.mobile_number AS "Mobile Number",
               c.id_proof AS "ID Proof",
               c.area AS "Area",
               c.ip_address AS "IP Address",
               c.mac_address AS "MAC Address",
               c.package_type AS "Package Type",
               c.name AS Name,
               c.activation_date AS "Activation Date",
               c.status AS "Status",
               c.installation_cost AS "Installation Cost",
               c.totalamount_paid AS "Total Amount Paid",
               c.monthly_amount AS "Monthly Amount",
               c.lastpaid_date AS "Last Paid Date",
               c.lastpaid_amount AS "Last Paid Amount",
               c.nextpay_date AS "Next Pay Date",
               c.totaldue_amount AS "Total Due Amount",
               t.agent_id AS "Agent ID",
               t.token_number AS "Token Number",
               t.machine_id AS "Machine ID"
          FROM customer c INNER JOIN transaction t ON c.customer_id = t.customer_id
        WHERE DATE(t.paid_date)BETWEEN '" + fromDate + "'AND '" + toDate + "' ";

But here the problem is:

How many times the record exists in transaction table,those times its giving records , But i need only once.

How can i do this ?

Upvotes: 0

Views: 93

Answers (4)

Mayuri
Mayuri

Reputation: 412

query ='SELECT c.customer_id AS "Customer ID", c.father_name AS "Father Name", c.address AS Address, c.phone_number AS "Phone Number", c.mobile_number AS "Mobile Number", c.id_proof AS "ID Proof", c.area AS "Area", c.ip_address AS "IP Address", c.mac_address AS "MAC Address", c.package_type AS "Package Type", c.name AS Name, c.activation_date AS "Activation Date", c.status AS "Status", c.installation_cost AS "Installation Cost", c.totalamount_paid AS "Total Amount Paid", c.monthly_amount AS "Monthly Amount", c.lastpaid_date AS "Last Paid Date", c.lastpaid_amount AS "Last Paid Amount", c.nextpay_date AS "Next Pay Date", c.totaldue_amount AS "Total Due Amount", t.agent_id AS "Agent ID", t.token_number AS "Token Number", t.machine_id AS "Machine ID" FROM customer c INNER JOIN transaction t ON c.customer_id = t.customer_id WHERE DATE(t.paid_date)BETWEEN '" + fromDate + "'AND '" + toDate + "' " GROUP BY c.customer_id;

Upvotes: 0

Rachcha
Rachcha

Reputation: 8816

The problem is with your query logic. There are three columns that you are selecting from transaction table. Now, for one single customer, there can be multiple transactions. If you are selecting t.agent_id, t.token_number and t.machine_id, this will give you these values for every transaction made by the customer.

If your business logic permits this, remove these three columns from the select list and use an EXISTS clause instead of an INNER JOIN like this:

query ='SELECT c.customer_id AS "Customer ID",
               c.father_name AS "Father Name",
               c.address AS Address,
               c.phone_number AS "Phone Number",
               c.mobile_number AS "Mobile Number",
               c.id_proof AS "ID Proof",
               c.area AS "Area",
               c.ip_address AS "IP Address",
               c.mac_address AS "MAC Address",
               c.package_type AS "Package Type",
               c.name AS Name,
               c.activation_date AS "Activation Date",
               c.status AS "Status",
               c.installation_cost AS "Installation Cost",
               c.totalamount_paid AS "Total Amount Paid",
               c.monthly_amount AS "Monthly Amount",
               c.lastpaid_date AS "Last Paid Date",
               c.lastpaid_amount AS "Last Paid Amount",
               c.nextpay_date AS "Next Pay Date",
               c.totaldue_amount AS "Total Due Amount" /*,
               t.agent_id AS "Agent ID",
               t.token_number AS "Token Number",
               t.machine_id AS "Machine ID" -- */
          FROM customer c 
        WHERE EXISTS (SELECT 1
                        FROM transaction t
                       WHERE c.customer_id = t.customer_id
                         AND DATE(t.paid_date)BETWEEN '"
                                                        + fromDate
                                                        + "'AND '"
                                                        + toDate + "' "

Now, if your business does need those three values, you can simply use a SELECT DISTINCT and stop worrying about duplicate values.

Upvotes: 1

blue
blue

Reputation: 1949

... GROUP BY c.customer_id

You'd most likely want to SUM some of the columns or use other aggregate functions.

Upvotes: 1

Sahil Mittal
Sahil Mittal

Reputation: 20753

Try this-

query = " ... ... ... GROUP BY c.customer_id";

Upvotes: 0

Related Questions