Proble to retrieve data from multiple tables my query is given

[jamshed ahmed]

SELECT Customer_Info.CustName,Customer_Info.Address,Customer_Bank_Info.CustChequeNo,Customer_Bank_Info.CustAccountNo
from Customer_Info,Customer_Bank_Info
WHERE Customer_Info.AgentID=Customer_Bank_Info.AgentID

THERE ARE TWO RECRODS ARE AVAILABLE IN Customer_Info TABLE AND TWO RECORDS ARE AVAILABLE IN Customer_Bank_Info BUT WHEN ABOVE QUERY IS EXECUTED IT SHOWS 4 RECORDS IT REPEATS THE QUERY THAT'S WHY SAME RECORDS FROM THESE TABLE ARE RETRIVED FOR TWO TIMES. OUT PUT IS GIVEN AS BELOW

CUST_NAME ADDRESS CHQUE_NO ACCOUNTNO
jam khi 11 44
nazia khi 22 55
jam khi 11 44>repeated
nazia khi 22 55 >repeated

data in Customer_Info table
CUST_NAME ADDRESS AgentID
25 jam khi emp11
26 nazia khi emp11

data in Customer_Bank_Info table
CHQUE_NO ACCOUNTNO AgentID
25 11 44 emp11
26 22 55 emp11

PLZZZ HELP ME

[urtrivedi]

i am assuming that you have customer_id column in both tables. I have joined using customer_id instead of agent_id

SELECT Customer_Info.CustName,Customer_Info.Address,Customer_Bank_Info.CustChequeNo,Customer_Bank_Info.CustAccountNoFROM Customer_Info,Customer_Bank_InfoWHERE Customer_Info.AgentID=Customer_Bank_Info.AgentIDSELECT Customer_Info.CustName,Customer_Info.Address,Customer_Bank_Info.CustChequeNo,Customer_Bank_Info.CustAccountNo
from Customer_Info,Customer_Bank_Info
WHERE Customer_Info.customerID=Customer_Bank_Info.customerID

[urtrivedi]

above one was copied twice follwing is the correct query

SELECT Customer_Info.CustName,Customer_Info.Address,Customer_Bank_Info.CustChequeNo,Customer_Bank_Info.CustAccountNoFROM Customer_Info,Customer_Bank_InfoWHERE Customer_Info.customerID=Customer_Bank_Info.customerID

[agongpor]

SELECT Customer_Info.CustName,Customer_Info.Address,Customer_Bank_Info.CustChequeNo,Customer_Bank_Info.CustAccountNo
from Customer_Info,Customer_Bank_Info
WHERE Customer_Info.AgentID=Customer_Bank_Info.AgentID

THERE ARE TWO RECRODS ARE AVAILABLE IN Customer_Info TABLE AND TWO RECORDS ARE AVAILABLE IN Customer_Bank_Info BUT WHEN ABOVE QUERY IS EXECUTED IT SHOWS 4 RECORDS IT REPEATS THE QUERY THAT'S WHY SAME RECORDS FROM THESE TABLE ARE RETRIVED FOR TWO TIMES. OUT PUT IS GIVEN AS BELOW

CUST_NAME ADDRESS CHQUE_NO ACCOUNTNO jam khi 11 44 nazia khi 22 55 jam khi 11 44>repeated nazia khi 22 55 >repeated

data in Customer_Info table CUST_NAME ADDRESS AgentID 25 jam khi emp11 26 nazia khi emp11

data in Customer_Bank_Info table CHQUE_NO ACCOUNTNO AgentID 25 11 44 emp11 26 22 55 emp11 PLZZZ HELP ME

Maybe its because the AgentID in every record is the same (emp11) thats why every single record in table A has two result.
You could add another field as the link beside the AgentID. (for example you could add the field AccountNO to table Customer_Info as the second link)

I hope it helps ...

[jamshed ahmed]

SELECT Customer_Info.CustName,Customer_Info.Address,Customer_Bank_Info.CustChequeNo,Customer_Bank_Info.CustAccountNoFROM Customer_Info,Customer_Bank_InfoWHERE Customer_Info.AgentID=Customer_Bank_Info.AgentIDSELECT Customer_Info.CustName,Customer_Info.Address,Customer_Bank_Info.CustChequeNo,Customer_Bank_Info.CustAccountNo
from Customer_Info,Customer_Bank_Info
WHERE Customer_Info.AgentID=Customer_Bank_Info.AgentID

THERE ARE TWO RECRODS ARE AVAILABLE IN Customer_Info TABLE AND TWO RECORDS ARE AVAILABLE IN Customer_Bank_Info BUT WHEN ABOVE QUERY IS EXECUTED IT SHOWS 4 RECORDS IT REPEATS THE QUERY THAT'S WHY SAME RECORDS FROM THESE TABLE ARE RETRIVED FOR TWO TIMES. OUT PUT IS GIVEN AS BELOW

CUST_NAME ADDRESS CHQUE_NO ACCOUNTNO
jam khi 11 44
nazia khi 22 55
jam khi 11 44>repeated
nazia khi 22 55 >repeated

data in Customer_Info table
CUST_NAME ADDRESS AgentID
25 jam khi emp11
26 nazia khi emp11

data in Customer_Bank_Info table
CHQUE_NO ACCOUNTNO AgentID
25 11 44 emp11
26 22 55 emp11

PLZZZ HELP ME WHAT WHOULD I DO TO RETRIVE ONLY TWO RECRODS THAT IS JAM AND NAZIA

[luap599]

Can you post the two tables showing all the columns, so I can help write the correct code for you!

Paul

SELECT Customer_Info.CustName,Customer_Info.Address,Customer_Bank_Info.CustChequeNo,Customer_Bank_Info.CustAccountNoFROM Customer_Info,Customer_Bank_InfoWHERE Customer_Info.AgentID=Customer_Bank_Info.AgentIDSELECT Customer_Info.CustName,Customer_Info.Address,Customer_Bank_Info.CustChequeNo,Customer_Bank_Info.CustAccountNo
from Customer_Info,Customer_Bank_Info
WHERE Customer_Info.AgentID=Customer_Bank_Info.AgentID

THERE ARE TWO RECRODS ARE AVAILABLE IN Customer_Info TABLE AND TWO RECORDS ARE AVAILABLE IN Customer_Bank_Info BUT WHEN ABOVE QUERY IS EXECUTED IT SHOWS 4 RECORDS IT REPEATS THE QUERY THAT'S WHY SAME RECORDS FROM THESE TABLE ARE RETRIVED FOR TWO TIMES. OUT PUT IS GIVEN AS BELOW

CUST_NAME ADDRESS CHQUE_NO ACCOUNTNO jam khi 11 44 nazia khi 22 55 jam khi 11 44>repeated nazia khi 22 55 >repeated

data in Customer_Info table CUST_NAME ADDRESS AgentID 25 jam khi emp11 26 nazia khi emp11

data in Customer_Bank_Info table CHQUE_NO ACCOUNTNO AgentID 25 11 44 emp11 26 22 55 emp11

PLZZZ HELP ME WHAT WHOULD I DO TO RETRIVE ONLY TWO RECRODS THAT IS JAM AND NAZIA

[agongpor]

Maybe you could add this script to the last row:

GROUP BY Customer_Info.CustName

So it will become like this:

SELECT Customer_Info.CustName,Customer_Info.Address,Customer_Bank_Info.CustChequeNo,Customer_Bank_Info.CustAccountNoFROM Customer_Info,Customer_Bank_InfoWHERE Customer_Info.AgentID=Customer_Bank_Info.AgentIDSELECT Customer_Info.CustName,Customer_Info.Address,Customer_Bank_Info.CustChequeNo,Customer_Bank_Info.CustAccountNo
from Customer_Info,Customer_Bank_Info
WHERE Customer_Info.AgentID=Customer_Bank_Info.AgentID
GROUP BY Customer_Info.CustName

Hope it helps ... :)

[jamshed ahmed]

Can you post the two tables showing all the columns, so I can help write the correct code for you! Paul

ok my friend the pic shows that when query is executed it retrieves 4 records but i guess it should show only 2 records so plzzz do something about it my all the work has stuck on this point

[luap599]

Hi This code will only shoe 2 records. The agent and two customer names. is this what you want?

SELECT
`dba_agent`.`agent_name`
, `dba_customer`.`cust_name`
FROM
`sample`.`dba_agent`
INNER JOIN `sample`.`dba_customer`
ON (`dba_agent`.`agent_id` = `dba_customer`.`agent_id`);

Paul

[luap599]

Hi This code will only shoe 2 records. The agent and two customer names. is this what you want?

SELECT `dba_agent`.`agent_name` , `dba_customer`.`cust_name` FROM `sample`.`dba_agent` INNER JOIN `sample`.`dba_customer` ON (`dba_agent`.`agent_id` = `dba_customer`.`agent_id`);

Paul

Sample is the table name i had used to test this code!

[drjohn]

The query is returning the CORRECT data

Because the join is on agentID and there is no link between the customer and the product, but there is a link between the agent and the TWO products.

so the join of agent to product produces two rows, and EACH row then joins to the customer with the same agentID, and there are two of them, so you get four rows.

In other words, your tables are wrong.

You are relating ALL customers of each agent to ALL products of each agent.
Don't customer buy/use products, shouldn't you have some table which lists which customers are buying/using which products.
ie a customerProduct table with the customerID and the productId in it, to link each customer to their specific products.

It's called normalisation.

[tyson.crouch]

please send full table SQL create statements for both Customer_Info AND Customer_Bank_Info

[tyson.crouch]

First off, your tables SHOULD look like the following:

Customer Bank Info

CREATE TABLE IF NOT EXISTS `customer_bank_info` (
  `customerID` int(12) NOT NULL,
  `CustAccountNo` int(12) NOT NULL,
  `CustChequeNo` int(12) NOT NULL,
  KEY `customerID` (`customerID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Customer Info

CREATE TABLE IF NOT EXISTS `customer_info` (
  `customerID` int(11) NOT NULL AUTO_INCREMENT,
  `custname` varchar(50) NOT NULL,
  `Address` varchar(255) NOT NULL,
  PRIMARY KEY (`customerID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Constraints

ALTER TABLE `customer_bank_info`
  ADD CONSTRAINT `customer_bank_info_ibfk_1` FOREIGN KEY (`customerID`) 
  REFERENCES `customer_info` (`customerID`) 
  ON DELETE CASCADE
  ON UPDATE CASCADE;

Secondly, for the query you wish to run I would suggest the following:

CREATE
    ALGORITHM = UNDEFINED
    VIEW customer_details
    AS
        SELECT * 
        FROM customer_info
        NATURAL JOIN customer_bank_info
        USING (customerID);

THEN to use your view, simply call

SELECT * FROM customer_details

[jamshed ahmed]

the query is returning the correct data

because the join is on agentid and there is no link between the customer and the product, but there is a link between the agent and the two products.

So the join of agent to product produces two rows, and each row then joins to the customer with the same agentid, and there are two of them, so you get four rows. in other words, your tables are wrong.

you are relating all customers of each agent to all products of each agent. Don't customer buy/use products, shouldn't you have some table which lists which customers are buying/using which products. Ie a customerproduct table with the customerid and the productid in it, to link each customer to their specific products.

It's called normalisation.

thx for your sharing me such use full knowledge i have changed all the realation in normalization form and now it works fine

[jamshed ahmed]

first off, your tables should look like the following:

customer bank info

create table if not exists `customer_bank_info` (
  `customerid` int(12) not null,
  `custaccountno` int(12) not null,
  `custchequeno` int(12) not null,
  key `customerid` (`customerid`)
) engine=innodb default charset=latin1;

customer info

create table if not exists `customer_info` (
  `customerid` int(11) not null auto_increment,
  `custname` varchar(50) not null,
  `address` varchar(255) not null,
  primary key (`customerid`)
) engine=innodb default charset=latin1 auto_increment=1 ;

constraints

alter table `customer_bank_info`
  add constraint `customer_bank_info_ibfk_1` foreign key (`customerid`) 
  references `customer_info` (`customerid`) 
  on delete cascade
  on update cascade;

secondly, for the query you wish to run i would suggest the following:

create
    algorithm = undefined
    view customer_details
    as
        select * 
        from customer_info
        natural join customer_bank_info
        using (customerid);

then to use your view, simply call

select * from customer_details

yes you are right database that i designed was wrong but now i have changed it and it works fine thx for shareing me such very use full knowledge