Using alias of a query to join with another table MySQL

Question

I have 3 tables.

support_works = contain serial_number
kickscooters = contian serial_number, kickscooter_id(as primary id, and named id)
rents = contain kickscooter_id.

I am trying to get serial_number of support_works that satisfy conditions then match it with kickscooter's serial_number. Then get its kickscooter_id to get all rents that match kickscooter_id retrieved from it.

Currently:

select k.id
     from support_works sw
     join kickscooters k
       on k.serial_number = sw.serial_number
     where
       sw.work_type = 'deploy' and
       (sw.updated_at between '2019-11-01 02:00:00' and '2019-11-01 10:00:00') and 
       k.id in (select kcu.kickscooter_id
                from kickscooter_control_units kcu
                where kcu.particle_product_id in (9358, 9383)));

this works perfectly on getting kickscooter_id from kickscooter table. But I am now using this as subquery to get all rents table data where rents.kickscooter_id is in this subquery:

select *
from rents r
where r.kickscooter_id
  in (select k.id
     from support_works sw
     join kickscooters k
       on k.serial_number = sw.serial_number
     where
       sw.work_type = 'deploy' and
       (sw.updated_at between '2019-11-01 02:00:00' and '2019-11-01 10:00:00') and 
       k.id in (select kcu.kickscooter_id
                from kickscooter_control_units kcu
                where kcu.particle_product_id in (9358, 9383)));

This is taking too long and I want to use multiple joins to make things faster. How can I go about it?

I have been using CTE however I've read that it takes up memory while creating/deleting temporary table therefore tried to avoid it.

Ed Bangga · Accepted Answer · 2019-11-12 01:49:35Z

1

You can join kickscooter_control_units on your subquery the use exists keyword rather than IN.

select *
from rents r
where 
  exists 
  (select 1
     from support_works sw
     join kickscooters k on k.serial_number = sw.serial_number
     join kickscooter_control_units kcu on  kcu.kickscooter_id =  k.id and kcu.particle_product_id in (9358, 9383)   
     where
       sw.work_type = 'deploy' and
       (sw.updated_at between '2019-11-01 02:00:00' and '2019-11-01 10:00:00'))

Seems based on your scenario you are filtering the ID. exists only applicable if you want to check if certain subquery contains values.

 select *
    from rents r
    where 
      r.kickscooter_id in 
      (select k.id
         from support_works sw
         join kickscooters k on k.serial_number = sw.serial_number
         join kickscooter_control_units kcu on  kcu.kickscooter_id =  k.id and kcu.particle_product_id in (9358, 9383)   
         where
           sw.work_type = 'deploy' and
           (sw.updated_at between '2019-11-01 02:00:00' and '2019-11-01 10:00:00'))

edited Nov 12, 2019 at 1:49

answered Nov 12, 2019 at 1:26

Ed Bangga

13k4 gold badges18 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

haneulkim Over a year ago

kcu.particle_product_id in (9358, 9383) correctly filters? you can do that in join clause?

haneulkim Over a year ago

could you tell me what select 1 is doing? when I run just that part it all returns 1.

Ed Bangga Over a year ago

using exists keyword doesn't care on your columns, as long there's results.

haneulkim Over a year ago

so having 1 instead of result is more efficient? what is the reason for it.

Ed Bangga Over a year ago

its more faster

|

Collectives™ on Stack Overflow

Using alias of a query to join with another table MySQL

1 Answer 1

11 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related