samedi 21 octobre 2017

how to fetch data by a conditional query(left join/right join/inner join/ multi select, ... or) from multi table

i have a problem with conditional query and can't solve that and i need your help. i use mysql with innodb and i have some tables like bellow.

table1: ---------------------- uers_type_1 ----------------------

user_id int(11) primary auto_increment
firstname varchar(15)
lastname varchar(20)
.
.
.

table 2: ---------------------- uers_type_2 ----------------------

user_id int(11) primary auto_increment
firstname varchar(15)
lastname varchar(20)
.
.
.

table 3: ---------------------- user_request ----------------------

request_id int(11) primary auto_increment
user_type enum("ut1","ut2")
user_id int(11)
request text

now i need a query to fetch something like this: user request data with user first name and last name from users type tables

Result:

-----------------------------------------------
- firstName - lastName - request_id - request -
-----------------------------------------------
- Robert    - De Niro  - 10         - some request.
- Will      - Smith    - 93         - some request.
.
.
.

my query something like this but it's not work

SELECT r.request_id , r.request, (
 execute(
  concat('select u.first_name AS firstName, u.lastname AS lastName from ', 
           (SELECT CASE r.user_type WHEN 'ut1' 
           THEN 'uers_type_1' WHEN 'ut2' THEN 'uers_type_2' END), 
           ' u WHERE u.user_id = ', r.user_id, ''
  )
 )
)
FROM `user_request` r
WHERE 1

Aucun commentaire:

Enregistrer un commentaire