CODEWITHSUNDEEP
mysql
Sylwekqaz
Sylwekqaz

Reputation: 329

select defalaut value if row not exist AND select from multiple tables if row not exist in secont table

I have two tables

Permission
|id|userID|canView|canEdit|
|--+------+-------+-------|
|1 |  0   | true  | false |  (my default value)
|2 |  1   | true  | true  |

Users
|id|name |
|--+-----|
|1 |Smith|

How select all rows from Permission and add name column from users table like this 

    |id|userID|canView|canEdit|name |
    |--+------+-------+-------+-----|
    |1 |  0   | true  | false |NULL | 
    |2 |  1   | true  | true  |Smith|

i try 

SELECT p.* , u.name FROM `Permission` p ,`Users` u WHERE u.id = p.userID

but its only return 

    |id|userID|canView|canEdit|name |
    |--+------+-------+-------+-----|
    |2 |  1   | true  | true  |Smith|

Upvotes: 0

Views: 60

Answers (1)

Ende Neu
Ende Neu

Reputation: 15783

One way would be to use a LEFT JOIN, this will fill from the column from the right table with nulls if no match is found:

SELECT 
  p.* , 
  u.name 
FROM `Permission` p 
LEFT JOIN `Users` u 
ON p.userID = u.id

Upvotes: 2

Related Questions