Subqueries and Union

Question

I have the 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 to create a view to populate a jquery autocomplete so I made the next query


    SELECT name, 'hotel' AS type, (SELECT id FROM destination WHERE hotel.destination_id = destination.id) AS destination_id FROM hotel 
    UNION  
    SELECT name, 'place' AS type, (SELECT id FROM destination WHERE place.destination_id = destination.id) AS destination_id FROM place
    UNION 
    SELECT name, 'alias' AS type, (SELECT destination.id FROM destination,hotel WHERE hotel_alias.hotel_id = hotel.id AND hotel.destination_id = destination.id) AS destination_id FROM hotel_alias

which returns the 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 are correct, but I wonder if Im using "subqueries" and "union" correctly or if is there a better way to generate the same result by optimizing the query?

Gracias!

Your query gives you the results you want, which means you've used them just fine - but yes, you can do it another way, without using dependent subqueries (which get executed for EVERY row in the outer query) — pala_, Apr 29 '15 at 06:54

score 0 · Answer 1 · answered Apr 29 '15 at 05:30

0

You could make use of JOIN

Have a look at: http://dev.mysql.com/doc/refman/4.1/en/join.html

answered Apr 29 '15 at 05:30

Akzwitch

113
1
2
13

pala_ · Answer 2 · 2015-04-29T22:51:31.570

You can get the results you want without resorting to multiple dependent subqueries, by performing your union first, and then inner joining to the destination table to ensure the destination exists and is valid.

select q.name, q.type, d.id  
  from (        
    select name, 'hotel' as type, destination_id as d_id
      from hotel
    union all
    select name, 'place' as type, destination_id as d_id
      from place
    union all
    select ha.name, 'alias' as type, destination_id as d_id
      from hotel_alias ha
        inner join hotel h
          on ha.hotel_id = h.id
   ) q
   inner join destination d
     on q.d_id = d.id

no dependent subqueries, union all to avoid a distinct purge, same result as your query. demo here.

Since you want to use this as a view, you can either expand the query out to this:

select hotel.name, 'hotel' as type, destination_id as d_id
  from hotel
    inner join destination d1
      on hotel.destination_id = d1.id
union all
select place.name, 'place' as type, destination_id as d_id
  from place
    inner join destination d2
      on place.destination_id = d2.id
union all
select ha.name, 'alias' as type, destination_id as d_id
  from hotel_alias ha
    inner join hotel h
      on ha.hotel_id = h.id
    inner join destination d3
      on h.destination_id = d3.id

Or to use the original query, split it into two views

create view v1 as 
  select name, 'hotel' as type, destination_id as d_id
    from hotel
  union all
    select name, 'place' as type, destination_id as d_id
      from place
  union all
    select ha.name, 'alias' as type, destination_id as d_id
      from hotel_alias ha
        inner join hotel h
          on ha.hotel_id = h.id

and then

create view YOUR_VIEW as
  select v1.name, v1.type, d.id
    from v1
      inner join destination d
        on v1.d_id = d.id
  order by type, name;

and then

select * from YOUR_VIEW

will give you the results you want.

updated fiddle that shows all the above options.

http://dev.mysql.com/doc/refman/5.0/en/create-view.html The SELECT statement cannot contain a subquery in the FROM clause. — Miguel Alejandro Lazcano, Apr 29 '15 at 16:12
@MiguelAlejandroLazcano I didn't include the view part as it wasn't contained in the select query, so i assumed it was simply language. It is trivial to modify to succeed as a view tho. i'll do so now — pala_, Apr 29 '15 at 22:44
@MiguelAlejandroLazcano there you go. in view form, with updated demo to check — pala_, Apr 29 '15 at 22:53

score -1 · Answer 3 · answered Apr 29 '15 at 05:12

-1

Whatever you have done for getting the result is correct. There is no any other way way to generate the same result.

answered Apr 29 '15 at 05:12

Daemon Thread

21
3

what? he could join instead of using a subquery. so there's at _least_ one other way – pala_ Apr 29 '15 at 05:13
with the help of join he can get only common records. The records that are avail in all the tables will not come. But, he wants all the records from all three tables. So the join operation will not work. @pala_ – Daemon Thread Apr 29 '15 at 06:28
yes. which is accomplished with joins + union. which is exactly what i said, use joins instead of subqueries. i didnt say use joins ONLY. – pala_ Apr 29 '15 at 06:30
what is the benefit of using join + union. In order to get benefit the processing will be slow and you will not get the desired output. Even the query will be too long. @pala_ – Daemon Thread Apr 29 '15 at 06:36
you will not get the output that you want means there is no other way @pala_ – Daemon Thread Apr 29 '15 at 06:43
... except you can get the desired output, which means there _is_ another way. – pala_ Apr 29 '15 at 06:58
If don't know the things then go and read first. Don't degrade the ans. @pala_ – Daemon Thread Apr 29 '15 at 07:00
If you dont want your answer downvoted, do not put incorrect information in it. If you doubt my claim that there is a different way to do it, feel free to examine my posted answer and demo fiddle. – pala_ Apr 29 '15 at 07:04
http://stackoverflow.com/questions/905379/what-is-the-difference-between-join-and-union read it out and complete your half knowledge @pala_ – Daemon Thread Apr 29 '15 at 07:08
The problem here is not with my knowledge of SQL, but rather with your comprehension. again, I refer you to my answer to this question – pala_ Apr 29 '15 at 07:22

Subqueries and Union

3 Answers3