users(id, username)
roles(id, name)
roles_users(user_id, role_id)
mysql> select id, username from users;
+----+----------+
| id | username |
+----+----------+
| 1 | admin |
| 9 | nema |
| 2 | tester |
| 6 | tester1 |
| 7 | tester2 |
| 8 | tester3 |
+----+----------+
mysql> select id, name from roles;
+----+-------+
| id | name |
+----+-------+
| 2 | admin |
| 1 | login |
+----+-------+
mysql> select user_id, role_id from roles_users;
+---------+---------+
| user_id | role_id |
+---------+---------+
| 1 | 1 |
| 7 | 1 |
| 8 | 1 |
| 1 | 2 |
| 2 | 2 |
+---------+---------+
Kako da izlistam korisnike sa statusom, tj da li imaju login role ili ne (login role oznacava da li je korisnik aktivan ili ne)? Dakle treba mi izlaz nesto kao:
+----+----------+-------+
| id | username | active|
+----+----------+-------+
| 1 | admin | TRUE |
| 9 | nema | FALSE |
| 2 | tester | FALSE |
| 6 | tester1 | FALSE |
| 7 | tester2 | TRUE |
| 8 | tester3 | TRUE |
+----+----------+-------+
Ja sam JOIN-ovao tabele, ali nikako da ubacim status, tj ili imam duplikate ili ako filtriram, onda mi nedostaju korisnici bez login role-a.
Is all your life will ever be
---Δ==
Neugodna istina - reaguj! | Linux is not Windows