Getting multiple values from a query

Question

I write a query to get values from 3 tables but this is returning multiple values so can any one tell where i went wrong

select c.CompanyName,cd.FedTaxID,cd.EmailAddress,cd.PhoneNumber
from tblcustomerdetail cd,tblcustomer c 
where c.FedTaxID in (
      select FedTaxID 
      from tblcustomer 
      where CustomerID in (
           select LOginID 
           from tbluserlogindetail 
           where UserName like "pa%" and RoleTypeID='20'
                          )
                     )
and cd.FedTaxID in (
      select FedTaxID 
      from tblcustomer
      where CustomerID in (
           select LOginID 
           from tbluserlogindetail
           where UserName like "pa%" and RoleTypeID='20'
                          )
                   );

My relation is here

My 3 tables are `tbluserlogindetails, tblcustomerdetails and tblCustomer'

1) Initially i will get `Login ID` from `tblUserLoginDetail ` based on the `user name`. 

2) Next based on `LoginID` i will get `FedTaxID` from tblcustomerDetail`

3) Next based on 'FedTaxID' i will get the the required details from `tblcustomer'

show some data and is there any relation between the two tables tblcustomerdetail and tblcustomer? — Kangkan, Aug 01 '11 at 08:00

score 1 · Accepted Answer · edited May 23 '17 at 12:19

SELECT 
    tblcustomer.CompanyName,
    tblcustomerdetail.FedTaxID,
    tblcustomerdetail.EmailAddress,
    tblcustomerdetail.PhoneNumber 

FROM tbluserlogindetail, tblcustomer, tblcustomerdetail
WHERE 
    tbluserlogindetail.LOginID = tblcustomer.CustomerID
    AND tblcustomer.FedTaxID = tblcustomerdetail.FedTaxID

    AND tbluserlogindetail.UserName LIKE 'pa%'
    AND tbluserlogindetail.RoleTypeID = '20'

Try something like this.

Subqueries have a slow perfomance.

MySQL - SELECT WHERE field IN (subquery) - Extremely slow why?

Getting multiple values from a query

1 Answers1