SQl queries left join not giving accurate results

Question

I have been trying to join two tables (USERS AND USERS_ROLES) based on their role id I put the left join on following query

users.id = users_roles.fk_user_id but the output is not correct of users_roles.fk_role_id coulmun and shows NULL where it should display the id of users.id = users_roles.fk_user_id that is 4 (at most places) because on users.id = users_roles.fk_user_id the value of users_roles.fk_role_id = 4

Kindly let me know how can i fix that so my query should result the exact vlaues of ids where they match, Thanks

SELECT users.id, users.v_first_name, users.v_last_name, user_facility.fk_facility_id,users.fk_tenant_id, marital_status.v_marital_status, 
            users.v_blood_type, NOW(),users_roles.fk_role_id
            FROM users
            LEFT JOIN (user_facility, marital_status, users_roles) ON
            users.id = user_facility.fk_user_id AND users.fk_marital_status_id=marital_status.id AND  users.id = users_roles.fk_user_id

score 2 · Answer 1 · answered Jun 21 '12 at 16:02

2

Usage of AND operator when used with Left or Right join gives different result. You should be clear what you are trying to accomplish..See this

answered Jun 21 '12 at 16:02

Shantanu Gupta

20,688
54
182
286

aF. · Answer 2 · 2012-06-21T16:28:43.563

1

When it shows NULL it means there isn't a correspondency (relation) between all tables in the JOIN clause.

If you want to show only the ones that have relations in all tables, use INNER JOIN instead.

SELECT u.id,
       u.v_first_name,
       u.v_last_name,
       uf.fk_facility_id,
       u.fk_tenant_id,
       ms.v_marital_status,
       u.v_blood_type,
       NOW(),
       ur.fk_role_id
FROM users u
INNER JOIN user_facility uf ON u.id = uf.fk_user_id
INNER JOIN marital_status ms ON u.fk_marital_status_id=ms.id
INNER JOIN users_roles ur ON u.id = ur.fk_user_id

edited Jun 21 '12 at 16:28

answered Jun 21 '12 at 15:59

aF.

64,980
43
135
198

okay means its happening because of other checks (i checked it by removing them and you are right) just one last question if i put insert on above to the mentioned query how can i put check as on this query that it checks and store only those data of month/year whose data wasn't entered in it before. Thanks, (If you say i'll post another question for it) – soft genic Jun 21 '12 at 16:15
@softgenic that might be another question. And you'll need to explain it better for all of us to understand it. – aF. Jun 21 '12 at 16:30
kindly check this url and see if you can help thanks, http://stackoverflow.com/questions/11144468/filteration-on-inserting-as-per-date-month-and-exsiting-record-of-userid – soft genic Jun 21 '12 at 18:36

Aprillion · Accepted Answer · 2012-06-21T16:49:43.000

well it is what you get by first implicitly inner-joining 3 tables and then explicitly left-joining the result to a 4th table only if 3 conditions relevant to all of the 3 inner-joinded tables are matched (i.e. when 3rd condition is false, nothing is joined from either of the 2 remaining tables)

i strongly suggest not to combine implicit and explicit joins, i personally use explicit joins all the time:

if you need an outer join:

SELECT ...
FROM users
LEFT JOIN user_facility ON users.id = user_facility.fk_user_id
LEFT JOIN marital_status ON users.fk_marital_status_id=marital_status.id
LEFT JOIN users_roles ON users.id = users_roles.fk_user_id

if you need an inner join:

SELECT ...
FROM users
JOIN user_facility ON users.id = user_facility.fk_user_id
JOIN marital_status ON users.fk_marital_status_id=marital_status.id
JOIN users_roles ON users.id = users_roles.fk_user_id

or if you prefere implicit inner joins for some obscure reason:

SELECT ...
FROM users,
     user_facility,
     marital_status,
     users_roles
WHERE users.id = user_facility.fk_user_id
  AND users.fk_marital_status_id=marital_status.id
  AND users.id = users_roles.fk_user_id

(implicit outer joins are getting deprecated in all RDBMS as far as i know)

Well thanks i am beginner and in a learning stage your answer is quite better now all the checks are being performed accordingly just the way i wanted and no null results by using explicit joins. So, from now onwards should i always use this style if i have to join with multiple tables? Is it a good coding practice? — soft genic, Jun 21 '12 at 16:37
[there](http://stackoverflow.com/q/44917/1176601) [is](http://stackoverflow.com/q/5654278/1176601) [much](http://stackoverflow.com/q/7810515/1176601) [debate](http://stackoverflow.com/q/6891422/1176601) considering implicit vs explicit joins, but as for combining them that would definitelly be bad coding practice — Aprillion, Jun 21 '12 at 16:46

SQl queries left join not giving accurate results

3 Answers3