Mysql - Returning table name in select query result

Question

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.

Begin by redesigning your table structure. Have one table with user information and include a column for something like a "user type". (Even better, a lookup table of user types and have a foreign key to that table.) The structure you have now is difficult to query and maintain, as you're discovering. — David
– David, Commented Apr 16, 2019 at 1:03
I've added a screenshot of the database layout for reference — Marcus Bennett
– Marcus Bennett, Commented Apr 16, 2019 at 12:37

Tim Biegeleisen · Accepted Answer · 2019-04-16 01:05:48Z

1

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.

answered Apr 16, 2019 at 1:05

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Marcus Bennett Over a year ago

The problem is each different user relates to the database in different ways and has different attributes. The tables included in the database are: Admin, Customer, Festival Organiser, Festival, Booking, Ticket, Performer and Performer_Festival. The only attributes each user shares are username and password

Marcus Bennett Over a year ago

I've added a screenshot of the database layout for reference

Tim Biegeleisen Over a year ago

This new information won't really change my answer.

Collectives™ on Stack Overflow

Mysql - Returning table name in select query result

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related