How do i build a mysql stored procedure to join one out of two tables based on different conditions-CodePudding

So let's say i have three tables

1)User 2)Employee 3)Client

user holds basic data of whether employee or client and has a column that mentions if that user is employee or client

I want to achieve fetching the users and joining each user to his corresponding table.

For example,if i fetch a user who is an employee, it should join that user to employee table. Likewise for client.

CodePudding user response：

You could join to both in one query, with conditions in the JOIN clause for each:

CREATE PROCEDURE ...
BEGIN
  SELECT ...
  FROM user AS u
  LEFT OUTER JOIN employee AS e
    ON u.id = e.user_id AND u.type='employee' 
  LEFT OUTER JOIN client AS c
    ON u.id = c.user_id AND u.type='client'; 
END

The user type can only have one value on a given row, so it may match employee or client, but it cannot match both. The one it doesn't match will return NULLs (that's how outer joins work).

Another strategy would be to use a CASE statement:

CREATE PROCEDURE myproc(IN in_userid INT)
BEGIN
  DECLARE user_type VARCHAR(10);

  SELECT type INTO user_type FROM user WHERE id = in_userid;

  CASE user_type
  WHEN 'employee' THEN
    SELECT ... FROM employee WHERE user_id = in_userid;
  WHEN 'client' THEN
    SELECT ... FROM client WHERE user_id = in_userid;
  ELSE
    -- should never happen
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = CONCAT('unknown user type', QUOTE(user_type));
  END;
END