select data from two tables

I have two tables Accountdetails and Accounts

• Accountdetails consists of columns AD_ID, AD_Name, AD_AccNo
• Accounts consists of Columns A_Name, A_AccNo, A_AccDate
开发者_如何学编程

My question is I wanted to select data based on the AD_AccNo and A_AccNo being equal

I tried using this query

select AD.AD_ID, a.A_Name, a.A_AccNo, a.A_AccDate 
from Accountdetails as AD, Accounts as a 
where AD.AD_AccNo = a.A_AccNo

Can anyone suggest me the place where i am going wrong?

---

select AD.AD_ID, a.A_Name, a.A_AccNo, a.A_AccDate 
from Accountdetails AD left join Accounts a 
ON AD.AD_AccNo = a.A_AccNo

---

Try

SELECT AD.AD_ID, a.A_Name, a.A_AccNo, a.A_AccDate from Accountdetails AD
LEFT JOIN Accounts a ON AD.AD_AccNo=a.A_AccNo
WHERE .... [your criteria here]

---

Using a join should work:

SELECT
    ad.AD_ID,
    a.A_Name,
    a.A_AccNo,
    a.A_AccDate
FROM
    Accountdetails ad
INNER JOIN
    Accounts a ON a.A_AccNo = ad.AccNo
WHERE
    [insert any conditional criteria here]    

---

There must be some duplicate account number values in the tables.

If you have 3 rows in Accountdetails with account number X and 4 rows in Accounts with that same number the join will add 12 rows to the result set.

To determine this try the following

select COUNT(*), AD_AccNo from Accountdetails GROUP BY AD_AccNo HAVING COUNT(*) > 1 

select COUNT(*), A_AccNo from Accounts GROUP BY A_AccNo HAVING COUNT(*) > 1

The resolution for this depends on why they are there. Is there an additional column you need to join on?

---

SELECT R.roleID, R.rolename, P.DisplayName, P.canRead, P.canWrite, P.canDelete
FROM role R
INNER JOIN (SELECT rp.roleId, rp.canDelete, rp.canRead, rp.canWrite, rp.moduleId,
            tm.DisplayName, tm.IsActive, tm.ParentId, tm.url
            FROM rolesPermissions rp
            INNER JOIN tbl_be_Modules tm ON rp.moduleId = tm.Id) P ON R.roleID = P.roleId

---

SELECT ad.AD_ID,
       ad.AD_Name,
       ad.AD_AccNo,
       a.A_Name,
       a.A_AccNo,
       a.A_AccDate 
FROM Accountdetails ad
INNER JOIN Accounts a
ON ad.AD_AccNo = a.A_AccNo  

---

That's the exact query, you're not doing a group by or something like that?