hemoali hemoali - 22 days ago 10
MySQL Question

Select from tables depend on row value

I would like to select data from 2 tables and the 2nd table is specified by a value stored in a 3rd table.
My tables are:


  1. users

  2. user_type

  3. Could be one of 5 tables (admins, buyers, sellers, accountants, deliverymen)



The required query (Which I don't how to achieve it) must:

I. Select a user from users table where
_id
equals some id.

II. We get the
user_type
(which is int value refers to user_type.type_id) field value (This table has
type_id
and names of the tables e.g. ["1"=>"buyers", "2"=>"sellers", ...])

III. Now we now what is the user type and the table name which contains more info about this user

IV. Select from the table (buyers, sellers, admins, ...) and retrieve the whole data (From table users and the other table (buyers, sellers, admins, ...etc) depending on user type) where table.user_id = users_id

I hope the flow makes sense and thank you (Any suggestions or alternative flows would be much appreciated)

Answer

As suggested by @Mak, I was able to implement the needed functionality as following:

CREATE DEFINER=`root`@`localhost` PROCEDURE `getUserData`(IN `user_id` INT)
BEGIN
DECLARE userTypeID INT;
DECLARE userTypeTableName TEXT;
    SELECT user_type into userTypeID FROM users WHERE users._id = user_id LIMIT 1;
    SELECT name into userTypeTableName FROM user_type WHERE user_type.type_id = userTypeID LIMIT 1;
    SET @sql_text = concat('select user.*, sp_user.* from users user JOIN ',userTypeTableName,' sp_user ON user._id = sp_user.user_id WHERE user._id = ', user_id);
    PREPARE stmt FROM @sql_text;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END
Comments