Proble to retrieve data from multiple tables my query is given

Question

jamshed ahmed 0 Junior Poster in Training

13 Years Ago

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

mysql

6 Contributors
14 Replies
150 Views
1 Week Discussion Span
Latest Post 13 Years Ago Latest Post by jamshed ahmed

All 14 Replies

urtrivedi 276 Nearly a Posting Virtuoso

13 Years Ago

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 276 Nearly a Posting Virtuoso

13 Years Ago

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 0 Newbie Poster

13 Years Ago

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 ...

Edited 13 Years Ago by agongpor because: n/a

luap599 0 Newbie Poster

13 Years Ago

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

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

jamshed ahmed 0 Junior Poster in Training · Answer 1 · 2010-05-10T16:20:47+00:00

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 0 Newbie Poster · Answer 2 · 2010-05-11T07:10:55+00:00

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 0 Junior Poster in Training · Answer 3 · 2010-05-11T10:51:41+00:00

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 0 Newbie Poster · Answer 4 · 2010-05-13T00:32:48+00:00

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 0 Newbie Poster · Answer 5 · 2010-05-13T13:06:11+00:00

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 56 Posting Pro in Training · Answer 6 · 2010-05-13T16:32:43+00:00

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 6 Junior Poster · Answer 7 · 2010-05-14T08:25:19+00:00

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

tyson.crouch 6 Junior Poster · Answer 8 · 2010-05-14T12:45:17+00:00

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 0 Junior Poster in Training · Answer 9 · 2010-05-14T16:02:03+00:00

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 0 Junior Poster in Training · Answer 10 · 2010-05-14T16:03:59+00:00

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

Proble to retrieve data from multiple tables my query is given

Recommended Answers Collapse Answers

All 14 Replies

Recommended Answers