CODEWITHSUNDEEP
mysqldatabasestored-procedures
Vinay Jeurkar
Vinay Jeurkar

Reputation: 3122

Stored procedure for combining multiple queries output

I have 4 database tables,

session :
------------------------------------------------------
| userid  | session_id   |  sess_type  |  sess_time  |
------------------------------------------------------
| 1       | 365672e3ab75 |  type-1     |  1463214751 |
| 2       | 2612fedcf78d |  type-2     |  1479111234 |
------------------------------------------------------

user :
----------------------------------
| userid  | name   |  user_type  |
----------------------------------
| 1       | Name-1 |  teacher    |
| 2       | Name-2 |  student    |
----------------------------------

teacher :
---------------------------------------------
| userid  | details-col1   |  details-col2  |
---------------------------------------------
| 1       | Value-1        |  Value-1       |
| 2       | Value-2        |  Value-2       |
---------------------------------------------

student :
---------------------------------------------
| userid  | student-col1   |  student-col2  |
---------------------------------------------
| 1       | Value-1        |  Value-1       |
| 2       | Value-2        |  Value-2       |
---------------------------------------------

Now, my requirement is :

  1. I want a row from "session" table which matches "session_id" field with the session_id value provided in stored procedure parameter.

  2. Also, I want a row in "user" table which matches its "userid" column value with the userid value from session table query output (specified above in point : 1).

  3. Now, based on the value stored in "user_type" column in "user" table query output, query should be fired on corresponding database table.

    For e.g. If the value in "user_type" column is "teacher", then query "teacher" table. Same for the value "student".

I can accomplish above requirement in 3 different queries by providing one query's output in second query. but, i want to accomplish it in one query/Stored-procedure only.

Please consider that Each of the database tables "user", "teacher", "student" may contain more than 1,000,000 rows in it. And both the tables "teacher" and "student" have more than 8 unique columns and may vary over the time.

What would be the most optmized stored Procedure to achieve this requirement?

Expected Output :

For Teacher :
---------------------------------------------------------------------------------------------------------------
| userid  | session_id   |  sess_type  |  sess_time  | name   |  user_type  | details-col1   |  details-col2  |
---------------------------------------------------------------------------------------------------------------
| 1       | 365672e3ab75 |  type-1     |  1463214751 | Name-1 |  teacher    | Value-1        |  Value-1       |
---------------------------------------------------------------------------------------------------------------

For Student : 
---------------------------------------------------------------------------------------------------------------
| userid  | session_id   |  sess_type  |  sess_time  | name   |  user_type  | student-col1   |  student-col2  |
---------------------------------------------------------------------------------------------------------------
| 1       | 365672e3ab75 |  type-1     |  1463214751 | Name-1 |  teacher    | Value-1        |  Value-1       |
---------------------------------------------------------------------------------------------------------------

Upvotes: 1

Views: 1194

Answers (1)

Barmar
Barmar

Reputation: 780974

If you need to select different columns, you can do this in a stored procedure:

SELECT u.user_type, se.session_id, se.sess_type, se.sess_time, u.userid, u.name, u.user_type 
    INTO @user_type, @session_id, @sess_type, @sess_time, @userid, @name, @type
FROM user AS u
JOIN session AS se ON se.userid = u.userid
WHERE se.session_id = @session_id_param;

IF @user_type = 'student'
THEN
    SELECT @userid AS userid, @session_id AS session_id, @sess_type AS sess_type, @sess_time AS sess_time, @name AS name, @type AS user_type, s.student_col1, s.student_col2
    FROM student AS s
    WHERE s.userid = @userid
ELSE
    SELECT @userid AS userid, @session_id AS session_id, @sess_type AS sess_type, @sess_time AS sess_time, , @name AS name, @type AS user_type, t.details_col1, t.details_col2
    FROM teacher AS t
    WHERE t.userid = @userid
END IF;

Upvotes: 2

Related Questions