select query with inner join

Question

I have two tables: dba_account and dba_account_password. Now I am trying to populate information from both tables.I am using this query.

SELECT da.account_name, da.account_id, da.status, da.manager_firstname, 
dap.live_password_change, dap.dev_password_change FROM dba_account da INNER JOIN 
dba_account_password dap ON da.account_id = dap.account_id

Now, the thing is dba_account has 1328 number of records(account_name), and dba_account_password only has 270 records. When I run this query it only displays 270 records. but I also want to display other usernames too(ones that are not in dba_account_password). any suggestions?

Answer 1

you can use left join in this case

SELECT da.account_name, da.account_id, da.status, da.manager_firstname, 
dap.live_password_change, dap.dev_password_change FROM dba_account da LEFT JOIN 
dba_account_password dap ON da.account_id = dap.account_id

Small description of left join

**

table 1
userid  username
1        ABC
2        PQR
3        DEF
table2
userid  userRole
1       ACCOUNTANT
2       HO

RESULT OF LEFT JOIN :-

 left join on table1.userid=table2.userid
    userid   username  userrole
    1        ABC        ACCOUNTANT
    2        PQR        HO
    3        DEF

** 

Answer 2

Change to a LEFT JOIN

SELECT da.account_name, 
    da.account_id, 
    da.status, 
    da.manager_firstname, 
    dap.live_password_change, 
    dap.dev_password_change 
FROM dba_account da 
LEFT JOIN dba_account_password dap 
    ON da.account_id = dap.account_id

An INNER JOIN will only give you the records that match in both tables. Here is a good Visual Explanation of Joins