How 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

[rickymartin3]

yes, the solution is

select * from emp where sal>5000 into #temp
select empname, sal, address from empDetails e, #temp t where e.loc=t.loc into #temp2
select * from #temp2

ok

[jamshed ahmed]

yes, the solution is

select * from emp where sal>5000 into #temp
select empname, sal, address from empDetails e, #temp t where e.loc=t.loc into #temp2
select * from #temp2

ok

THX FOR UR POST RICKY BUT WOULD YOU LIKE TO GIVE ME THE SOLUTION ABOT MY CODE PLZZZZ

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

WHAT I SIMPLY WANT TO RETRIEVE THE DATA FROM BOTH THE TABLES WHER THE AgentID IS SAME IN THES TWO TABLES

[rohand]

THX FOR UR POST RICKY BUT WOULD YOU LIKE TO GIVE ME THE SOLUTION ABOT MY CODE PLZZZZ

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

WHAT I SIMPLY WANT TO RETRIEVE THE DATA FROM BOTH THE TABLES WHER THE AgentID IS SAME IN THES TWO TABLES

hey dude try below one. I have modified your query.

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
Join Customer_Bank_Info on
Customer_Info.AgentID = Customer_Bank_Info.AgentID

Please mark it as solved if your problem get solved :-)

[jamshed ahmed]

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

WHAT I SIMPLE WANT THTAT I WANT TO VIEW CUTOMERS OF AGENT FROM DIFFERENT TABLES SUCH CUSTOMER BANK RECROD,CUSTOMER PERSONAL INFO,CUSTOMER ORDER DETAIL ETC.... I GUESS THERE IS SOMETHING WRONG WITH AagentID COULMN BECOSE IT IS AVAILABLE IN ALL THE TABLES THAT'S WHY IT RETRIVE SAME DATA FOR TWO TIMES
PLZZZ HELP ME

[rohand]

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

WHAT I SIMPLE WANT THTAT I WANT TO VIEW CUTOMERS OF AGENT FROM DIFFERENT TABLES SUCH CUSTOMER BANK RECROD,CUSTOMER PERSONAL INFO,CUSTOMER ORDER DETAIL ETC.... I GUESS THERE IS SOMETHING WRONG WITH AagentID COULMN BECOSE IT IS AVAILABLE IN ALL THE TABLES THAT'S WHY IT RETRIVE SAME DATA FOR TWO TIMES
PLZZZ HELP ME

have you tried the modified query i have posted ?

[sourabhtripathi]

try left or right join querry depending upon the priorities of tables !!

[jamshed ahmed]

hey dude try below one. I have modified your query.

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
Join Customer_Bank_Info on
Customer_Info.AgentID = Customer_Bank_Info.AgentID

Please mark it as solved if your problem get solved :-)

i want to retrive two records but it shous 4 records it repeats query i guess