0

I have two tables in my database which I try to combine through a view, where the id and user_id should match up. I have checked out UNION ALL, but this doesn't seem to be the solution since the order of the entities in the tables can be different and they should be matched.

users:

id    email              password
--------------------------------------
1     eric@test.com      sdgsdhdfhs
2     john@test.com      dfgsdgdfhg
3     mary@test.com      plkdfjvjvv

permissions:

index    user_id     allow_read   allow_write   allow_delete
-------------------------------------------------------------
1        2           1            0             1
2        3           1            1             1
3        1           0            0             0

which should become: (@Gordon Linoff)

id    email              password       allow_read   allow_write   allow_delete
------------------------------------------------------------------------------
1     eric@test.com      sdgsdhdfhs     0            0             0
2     john@test.com      dfgsdgdfhg     1            0             1
3     mary@test.com      plkdfjvjvv     1            1             1
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Rick
  • 89
  • 10

1 Answers1

1

You can certainly create a view with both the table attributes. The example has one-to-one mapping between two tables, i.e. for every id in users table there is exactly one row in permissions with user_id = id. If that is the case then inner join might work. If you only care about users table, you can use left join. I have tested this query, please find the fiddle:

CREATE VIEW joined_table_view AS
SELECT u.id, u.email, u.password, p.user_id, p.allow_read, p.allow_write, p.allow_delete
FROM users u
LEFT JOIN permissions p
ON u.id = p.user_id;

Further, if you want to know more joins refer here. Shall update sqlfiddle once I test it.

Community
  • 1
  • 1
PseudoAj
  • 5,234
  • 2
  • 17
  • 37
  • To be clear: It's all about the "users" table. IF available I want to add the columns of the "permissions" table. If not; then the columns should be just NULL. Their may not be an entity in the "permissions" table, but if there is, it is only 1. – Rick May 06 '17 at 01:19
  • 1
    Updated answer @Rick – PseudoAj May 06 '17 at 01:24
  • I have created a fiddle that shows the `LEFT JOIN` works @Rick – PseudoAj May 06 '17 at 02:07
  • I currently have * for SELECT, but how would I combine all columns but DO NOT combine/include "user_id"-column (from the LEFT JOIN table(s)) – Rick May 06 '17 at 12:06