selecting data from two tables
-
hi, I need to select data from two tables am using the following query:
select c.customer_id ,c.father_name ,c.address,c.phone_number ,c.mobile_number ,c.id_proof ,c.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,transaction t where c.customer_id=t.customer_id and DATE(t.paid_date)BETWEEN '2014-02-02'AND '2014-04-02' ;
It's giving correct information but each record is displayed twice , i want a record only once.
-
hi, I need to select data from two tables am using the following query:
select c.customer_id ,c.father_name ,c.address,c.phone_number ,c.mobile_number ,c.id_proof ,c.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,transaction t where c.customer_id=t.customer_id and DATE(t.paid_date)BETWEEN '2014-02-02'AND '2014-04-02' ;
It's giving correct information but each record is displayed twice , i want a record only once.
You have two matching rows in the
transactiontable for each customer, and you've asked SQL to return all of them, so it's hardly surprising that you get two rows back per customer. If you only want the most recent transaction for each customer, there are ways to do that. However, it would help if you told us which DBMS you're using. Also, try to avoid using old-style joins:... FROM customer c,transaction t WHERE c.customer_id = t.customer_id And ...
Use an
INNER JOINinstead:... FROM customer c INNER JOIN transaction t ON c.customer_id = t.customer_id WHERE ...
It makes it much easier to separate the joining conditions from the filtering conditions, and to switch between different types of join.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
hi, I need to select data from two tables am using the following query:
select c.customer_id ,c.father_name ,c.address,c.phone_number ,c.mobile_number ,c.id_proof ,c.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,transaction t where c.customer_id=t.customer_id and DATE(t.paid_date)BETWEEN '2014-02-02'AND '2014-04-02' ;
It's giving correct information but each record is displayed twice , i want a record only once.
I guess it's safe to assume that it's monthly payments, and that BETWEEN '2014-02-02'AND '2014-04-02' you'd have two payments per customer_id.
Wrong is evil and must be defeated. - Jeff Ello[^]
-
You have two matching rows in the
transactiontable for each customer, and you've asked SQL to return all of them, so it's hardly surprising that you get two rows back per customer. If you only want the most recent transaction for each customer, there are ways to do that. However, it would help if you told us which DBMS you're using. Also, try to avoid using old-style joins:... FROM customer c,transaction t WHERE c.customer_id = t.customer_id And ...
Use an
INNER JOINinstead:... FROM customer c INNER JOIN transaction t ON c.customer_id = t.customer_id WHERE ...
It makes it much easier to separate the joining conditions from the filtering conditions, and to switch between different types of join.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Mysql database we are using.yes my transaction table is having two records per customer id, but paid amounts and other fields are different , but here the query is giving last record values for two records having same customerid. Transaction table doesn't contain primary key,so there exists more than one record per customer id. and i want most recent record from Database.
-
hi, I need to select data from two tables am using the following query:
select c.customer_id ,c.father_name ,c.address,c.phone_number ,c.mobile_number ,c.id_proof ,c.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,transaction t where c.customer_id=t.customer_id and DATE(t.paid_date)BETWEEN '2014-02-02'AND '2014-04-02' ;
It's giving correct information but each record is displayed twice , i want a record only once.
Where do you display the result? In a .Net application written by you / your colleagues? In MySQL Query browser? ...?
-
Where do you display the result? In a .Net application written by you / your colleagues? In MySQL Query browser? ...?
am generating EXcel/ PDF file,in .net application written by me,
-
Mysql database we are using.yes my transaction table is having two records per customer id, but paid amounts and other fields are different , but here the query is giving last record values for two records having same customerid. Transaction table doesn't contain primary key,so there exists more than one record per customer id. and i want most recent record from Database.
I'm not overly familiar with MySql, but something like this should do the trick:
SELECT
...
FROM
customer c
INNER JOIN transaction t
ON t.customer_id = c.customer_id
INNER JOIN
(
SELECT customer_id, Max(paid_date) As LastPaid
FROM transaction
WHERE DATE(paid_date) BETWEEN '2014-02-02' AND '2014-04-02'
GROUP BY customer_id
) t2
ON t2.customer_id = t.customer_id
And t2.LastPaid = t.paid_date
;
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
am generating EXcel/ PDF file,in .net application written by me,
Consequently, the first thing to check is if the data returned from the query to your application are correct, and then where that duplication happens. That's a lot of debugging...
-
hi, I need to select data from two tables am using the following query:
select c.customer_id ,c.father_name ,c.address,c.phone_number ,c.mobile_number ,c.id_proof ,c.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,transaction t where c.customer_id=t.customer_id and DATE(t.paid_date)BETWEEN '2014-02-02'AND '2014-04-02' ;
It's giving correct information but each record is displayed twice , i want a record only once.
Quote:
