sql - Mysql get result in single query -

how find out results in single query

i have following 2 tables:

master_data_values table has foreign key of master_data table.

by combining 2 tables want "value" (from master_data_value) considered depending on 3 params - account_id, specility_id, user_id each "key"(from master_data table).

now have following priority

1.user_id

2.account_id

3.specility_id .

i have single query send above 3 params per request.

now suppose:

if request params not match should return value null's

example results

i querying user_id= 67 , specility_id = 55 , account_id = 4

then result return master_data_values below

now here key get_data got value yes , put_data got value no. here get_data there no matching criteria user_id, account_id, specility_id values provided me . took value master_data_values table has set null. while put_data matched user_id took value "no"

2nd example

suppose query params account_id = 1 , specility_id = 2 , user_id = null

then should give preference account_id , result should

here get_data got value yes. , put_data got value yes

here there 2 matching criteria's get_data i.e account_id , specility_id. per preference took value assigned account_id , "yes".

​note - ​ using mysql 5.7

script reference create table , sample data please execute them in order .

create database `test_schema` ;  ​create table `test_schema`.`master_data` (   `master_data_id` int(11) not null auto_increment,   `key` varchar(45) not null,   primary key (`master_data_id`) ) engine=innodb default charset=latin1;  insert `test_schema`.`master_data` (`key`) values ('get_data'); insert `test_schema`.`master_data` (`key`) values ('put_data');  create table `test_schema`.`master_data_values` (   `master_data_value_id` int(11) not null auto_increment,   `master_data_id` int(11) not null,   `value` varchar(45) null,   `account_id` int(11) null,   `specility_id` int(11) null,   `user_id` int(11) null,   primary key (`master_data_value_id`),   index `fk_master_data_values_1_idx` (`master_data_id` asc),   constraint `fk_master_data_values_1`     foreign key (`master_data_id`)     references `test_schema`.`master_data` (`master_data_id`)     on delete no action     on update no action);  insert `test_schema`.`master_data_values` (`master_data_id`, `value`, `account_id`) values ('1', 'yes', '1'); insert `test_schema`.`master_data_values` (`master_data_id`, `value`, `specility_id`) values ('1', 'no', '2'); insert `test_schema`.`master_data_values` (`master_data_id`, `value`) values ('1', 'yes'); insert `test_schema`.`master_data_values` (`master_data_id`, `value`) values ('2', 'yes'); insert `test_schema`.`master_data_values` (`master_data_id`, `value`, `user_id`) values ('2', 'no', '67');