CODEWITHSUNDEEP
mysqlsqldatabaseselect
hawkhorrow
hawkhorrow

Reputation: 475

SELECT from Union x 3 using filter of another table

Background

I have a web application which must remove entries from other tables, filtered through a selection of 'tielists' from table 1 -> item_table 1, table 2, table 3.... now basically my result set is going to be filthy big unless I use a filter statement from another table, using a user_id... so can someone please help me structure my statement as needed? TY!

Tables

cars_belonging_to_user
-----------------------------
ID | user_id | make   | model
----------------------------
1  |  1      | Toyota | Camry
2  |  1      |Infinity| Q55
3  |  1      | DMC    | DeLorean
4  |  2      | Acura  | RSX

Okay, Now the three 'tielists'
name:tielist_one
----------------------------
id | id_of_car | id_x | id_y|
1  | 1         | 12   | 22  |
2  | 2         | 23   | 32  |
-----------------------------
name:tielist_two
-------------------------------
id | id_of_car | id_x | id_z|
1  |  3        | 32   | 22  |
-----------------------------
name: tielist_three
id | id_of_car | id_x | id_a|
 1 | 4         | 45   | 2   |
------------------------------

Result Set and Code

echo name_of_tielist_table
// I can structure if statements to echo result sets based upon the name
// Future Methodology: if car_id is in tielist_one, delete id_x from x_table, delete id_y from y_table...
// My output should be a double select base:
--SELECT * tielists from WHERE car_id is 1... output name of tielist... then
--SELECT * from specific_tielist where car_id is 1.....delete x_table, delete y_table...

Considering the list will be massive, and the tielist equally long, I must filter the results where car_id(id) = $variable && user_id = $id....

Side Notes

  1. Only one car id will appear once in any single tielist..

  2. This select statement MUST be filtered with user_id = $variable... (and remember, i'm looking for which car id too)

  3. I MUST HAVE THE NAME of the tielist it comes from able to be echo'd into a variable...

  4. I will only be looking for one single id_of_car at any given time, because this select will be contained in a foreach loop.

  5. I was thinking a union all items would do the trick to select the row, but how can I get the name of the tielist the row is in, and how can the filter be used from the user_id row

Upvotes: 1

Views: 305

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

If you want performance, I would suggest left outer join instead of union all. This will allow the query to make efficient use of indexes for your purpose.

Based on what you say, a car is in exactly one of the lists. This is important for this method to work. Here is the SQL:

select cu.*,
       coalesce(tl1.id_x, tl2.id_x, tl3.id_x) as id_x,
       tl1.y, tl2.idz, tl3.id_a,
       (case when tl1.id is not null then 'One'
             when tl2.id is not null then 'Two'
             when tl3.id is not null then 'Three'
        end) as TieList
from Cars_Belonging_To_User cu left ouer join
     TieList_One tl1
     on cu.id_of_car = tl1.id_of_car left outer join
     TieList_Two tl2
     on cu.id_of_car = tl2.id_of_car left outer join
     TieList_Three tl3
     on cu.id_of_car = tl3.id_of_car;

You can then add a where clause to filter as you need.

If you have an index on id_of_car for each tielist table, then the performance should be quite good. If the where clause uses an index on the first table, then the joins and where should all be using indexes, and the query will be quite fast.

Upvotes: 1

Related Questions