How should joins used in mysql?
If i have two tables like
user table-"u"
userid | name
1 | lenova
2 | acer
3 | hp
pass table-"p"
userid | password
1 | len123
2 | acer123
3 | hp123
as for as i learnt from tutorials I can join these 2 tables using many joins available in mysql as said here
If i have a table like
role table-"r"
roleid | rname
1 | admin
2 | user
3 | dataanalyst
token table-"t"
tokenid| tname
1 | xxxx
2 | yyyy
3 | zzzz
tole_token_association table-"a"
roleid | tokenid
1 | 1
1 | 2
3 | 1
3 | 3
3 | 1
开发者_Python百科
I have to make a join such that I have to display a table which corresponds like this "rolename" has all these tokens.How to make this? I am confused. Is it possible to make a join? I am liking mysql a lot. I wish to play with queries such that not playing. I want to get well versed. Any Suggestions Please?
It's easiest to see when the column names that need to be joined are named identically:
SELECT r.rname,
t.tname
FROM ROLE r
JOIN ROLE_TOKEN_ASSOCIATION rta ON rta.roleid = r.roleid
JOIN TOKEN t ON t.tokenid = rta.tokenid
This will return only the roles with tokens associated. If you have a role that doesn't have a token associated, you need to use an OUTER join, like this:
SELECT r.rname,
t.tname
FROM ROLE r
LEFT JOIN ROLE_TOKEN_ASSOCIATION rta ON rta.roleid = r.roleid
JOIN TOKEN t ON t.tokenid = rta.tokenid
This link might help -- it's a visual representation of JOINs.
精彩评论