开发者

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?

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜