CODEWITHSUNDEEP
mysqlselect
Marcus Bennett
Marcus Bennett

Reputation: 3

Mysql - Returning table name in select query result

Database Layout I have a database that stores a bunch of info including the username, password and other details of different types of users (admin, customers etc.) in seperate tables of course.

And I want to search through the database using the username and return the name of the table that username belongs to. This will tell me what type of user they are. Something like this:

SELECT tableName FROM table1,table2,table3 WHERE username= thisValue

I don't have much experience with mysql so I don't even know where to begin.

Upvotes: 0

Views: 152

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520958

You said in seperate tables of course, but in practice it would probably be better to just have a single user table, with a role column which records the user's role:

CREATE TABLE users (
    id INT NOT NULL PRIMARY KEY,
    role_id INT NOT NULL,
    FOREIGN KEY (role_id) REFERENCES roles (id)
)

and a roles table:

CREATE TABLE roles (
    id INT NOT NULL PRIMARY KEY,
    desc VARCHAR(100) NOT NULL,
    ...
)

With this design in place, it would be fairly straightforward to find all users who are, e.g., admins:

SELECT u.*
FROM users u
INNER JOIN roles r
    ON u.role_id = r.id
WHERE
    r.desc = 'admin';

This answer assumes that a given user would only always have a single role. To account for multiple roles, we could use a junction table between the users and roles tables.

Upvotes: 1

Related Questions