CODEWITHSUNDEEP
sqlview
Rick
Rick

Reputation: 99

SQL VIEW combine tables

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     [email protected]      sdgsdhdfhs
2     [email protected]      dfgsdgdfhg
3     [email protected]      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     [email protected]      sdgsdhdfhs     0            0             0
2     [email protected]      dfgsdgdfhg     1            0             1
3     [email protected]      plkdfjvjvv     1            1             1

Upvotes: 0

Views: 72

Answers (1)

PseudoAj
PseudoAj

Reputation: 5951

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.

Upvotes: 1

Related Questions