CODEWITHSUNDEEP
sql
oskar2771
oskar2771

Reputation: 73

SQL query to select all users that have a store and not

I have 3 tables,

  1. users
  2. users_role
  3. users_store

    All users has a role but not all has a store. I'd like to select all users that with any roles and print out their stores but some users do not have any record about store in third table.

This is my sql query

select users.user_id, users.user_org, users.user_login, users_role.user_role, users_store.store_name 
from users, users_role, users_store     
WHERE users.user_login = users_role.user_login 
AND users.user_login = users_store.store_owner

By doing this i select only users that have a store but i need to select users with non store too

Upvotes: 0

Views: 112

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

Never use commas in the FROM clause. Always use proper, explicit, standard, readable JOIN syntax.

You need LEFT JOINs:

select u.user_id, u.user_org, u.user_login, ur.user_role, us.store_name 
from users u left join
     users_role ur
     on u.user_login = ur.user_login left join
     users_store us
     on u.user_login = us.store_owner;

Upvotes: 1

Related Questions