mysql - Subqueries and Union -


i have next mysql tables

      create table if not exists `my_app`.`hotel` (       `id` int not null auto_increment,       `name` varchar(45) not null,       `destination_id` int not null,       primary key (`id`),       index `idx_name` (`name` asc),       index `fk_hotel_destination1_idx` (`destination_id` asc),       constraint `fk_hotel_destination1`         foreign key (`destination_id`)         references `my_app`.`destination` (`id`)         on delete no action         on update no action)     engine = innodb  
      create table if not exists `my_app`.`hotel_alias` (       `id` int not null auto_increment,       `hotel_id` int not null,       `name` varchar(45) not null,       primary key (`id`),       index `idx_name` (`name` asc),       index `fk_hotel_alias_hotel_idx` (`hotel_id` asc),       constraint `fk_hotel_alias_hotel`         foreign key (`hotel_id`)         references `my_app`.`hotel` (`id`)         on delete no action         on update no action)     engine = innodb  
      create table if not exists `my_app`.`destination` (       `id` int not null auto_increment,       `name` varchar(45) not null,       primary key (`id`),       index `idx_name` (`name` asc))     engine = innodb  
      create table if not exists `my_app`.`place` (       `id` int not null auto_increment,       `name` varchar(45) not null,       `destination_id` int not null,       primary key (`id`),       index `idx_name` (`name` asc),       index `fk_place_destination1_idx` (`destination_id` asc),       constraint `fk_place_destination1`         foreign key (`destination_id`)         references `my_app`.`destination` (`id`)         on delete no action         on update no action)     engine = innodb

i want create view populate jquery autocomplete made next query

      select name, 'hotel' type, (select id destination hotel.destination_id = destination.id) destination_id hotel      union       select name, 'place' type, (select id destination place.destination_id = destination.id) destination_id place     union      select name, 'alias' type, (select destination.id destination,hotel hotel_alias.hotel_id = hotel.id , hotel.destination_id = destination.id) destination_id hotel_alias

which returns following results

      |name                       |type   |destination_id     ---------------------------------------------------      |hotel casa maya            |hotel  |1     |sandos caracol             |hotel  |2     |cabañas tulum              |hotel  |3     |sandos luxury              |hotel  |1     |ocean spa                  |hotel  |1     |sandos playacar            |hotel  |2     |walmart                    |place  |1     |walmart                    |place  |2     |centro                     |place  |3     |campo de golf pok-ta-pok   |place  |1     |sandos beach scape         |alias  |2     |sunset spa                 |alias  |1

the results correct, wonder if im using "subqueries" , "union" correctly or if there better way generate same result optimizing query?

gracias!

you make use of join

have at: http://dev.mysql.com/doc/refman/4.1/en/join.html


Comments

Post a Comment

Popular posts from this blog

php - failed to open stream: HTTP request failed! HTTP/1.0 400 Bad Request -

this code i'm using find latitude , longitude of place: function coords($address){ echo $url="http://maps.googleapis.com/maps/api/geocode/json?address=".$address; $json = file_get_contents(urlencode($url)); $data = json_decode($json, true); print_r($data['results'][0]['geometry']['location']['lat']); print_r($data['results'][0]['geometry']['location']['lng']); } however returns warning: failed open stream: http request failed! http/1.0 400 bad request if use above code in simple procedure works fine not in function. note: have tried curl_init()... method produced same result? instead of encoding whole url encode address part. following code work fine $add='jamshoro phase 2'; $url="http://maps.googleapis.com/maps/api/geocode/json?address=".urlencode($add); $json = file_get_contents($url); $data = json_decode($json, true); print_r($data['r
Read more

java - How to filter a backspace keyboard input -

i have code gives message when key number pressed: txtvalueofclause.addeventfilter(keyevent.key_typed, new eventhandler<keyevent>() { @override public void handle(keyevent t) { char ar[] = t.getcharacter().tochararray(); char ch = ar[t.getcharacter().tochararray().length - 1]; if (!(ch >= '0' && ch <= '9')) { system.out.println("the char entered not number"); t.consume(); } } }); now if press on wrong button , press backspace remove it, error message. how can add backspace input if statement? i have found answer: txtvalueofclause.addeventfilter(keyevent.key_typed, new eventhandler<keyevent>() { @override public void handle(keyevent t) { char ar[] = t.getcharacter().tochararray(); char ch = ar[t.getcharacter().tochar
Read more

java - Show Soft Keyboard when EditText Appears -

i have code in android app, alert dialog , keyboard, , when dialog shown want keyboard appear. not , not sure why? here code: final alertdialog.builder alert = new alertdialog.builder(this); alert.settitle("title"); alert.setmessage("message"); final edittext input = new edittext(this); input.setinputtype(inputtype.type_class_number); alert.setview(input); input.setonfocuschangelistener(new view.onfocuschangelistener() { @override public void onfocuschange(view v, boolean hasfocus) { if (hasfocus) { log.i(tag, "in focus"); inputmethodmanager inputmethodmanager = (inputmethodmanager) getsystemservice(context.input_method_service); inputmethodmanager.showsoftinput(input, inputmethodmanager.show_implicit); } } }); but keyboard not appear though edit text in focus, , tell appear why, doesn't come up? thanks in advance.
Read more