5

I have a table that contains a column that acts as a "flag" which is used to decide which table to pull additional information from (i.e. the value 1 pulls from table1, 2 from table2, etc). Usually I would just join the table using indexes/keys. However the table that I could join contained information that could be normalized into separate tables which leaves me to this situation of using a column to decide which table to join.

So here is my question, what is the most efficient way to join different tables based on the value produced in this column?

Here are the two ways I know how to accomplish this task currently. I am pretty sure they are both not the optimal solution:

  1. Pull the information from my main table (containing the column value that decides which table to join), and then through code in my application send additional queries to get the rest of the information.

  2. Go join crazy, return the columns of every table (even if unused). Then, through my code, ignore the nulls of the tables not needed.

Sathyajith Bhat
  • 21,321
  • 22
  • 95
  • 134
user1110302
  • 301
  • 1
  • 4
  • 11

3 Answers3

1

Definately not option 2. If you dont need the data dont retrieve it. Simple. It would be incredibly inefficient to join on tables (especially large ones) when you dont need the data. You could go with option 1 or use dynamic SQL to build up the query. I would then put some test cases together and run the execution plan to see how your query is performing.

Darren
  • 68,902
  • 24
  • 138
  • 144
1

Depending on the content of the other tables, I'd suggest a UNION - the columns returned need to be the same from each query. So you can do something like:

SELECT table1.title, tabel2.text FROM table1 INNER JOIN table2 ON table1.id=table2.id WHERE table1.key='2'
UNION
SELECT table1.title, tabel3.text FROM table1 INNER JOIN table3 ON table1.id=table3.id WHERE table1.key='3'

(Tweaking the SQL to make sure that it matches your schema, and indeed to avoid any mistakes I've added in)

andrewsi
  • 10,807
  • 132
  • 35
  • 51
  • Each table has different columns except for the keys/indexes I will be using to join the tables together, once I know which table to use. – user1110302 May 08 '12 at 04:35
  • In that case, I think you're going to have to make multiple queries, one for each table. Though might it be possible to CAST the rows from each table so they're all in the same types? – andrewsi May 08 '12 at 15:32
  • note with temporary tables they can not be used in subqueries/union more than once in mysql – tipu Feb 25 '14 at 22:29
1

I think it is possible:

create table a (id integer, flag boolean);
create table b (id integer, value_b varchar(30));
create table c (id integer, value_c varchar(30));

insert into a values (1, true), (2, false);
insert into b values (1, 'Val 1'), (2, 'Val 2');
insert into c values (1, 'C 1'), (2, 'C 2');

select a.id,
       case when a.flag then b.value_b else c.value_c end AS value
  from a
  left join b using (id)
  left join c using (id);

You can try it out.

Of course there're limitations:

  • number of columns is fixed, so you should go for NULLs if some values should be omitted;
  • you'll have to write a CASE ... END for each column;
  • you should know all joined tables in advance;
  • performance might not be the best.
vyegorov
  • 21,787
  • 7
  • 59
  • 73
  • Thanks for the response, but this definitely won't work for my case. In this case, the columns will be different on each table, and as other mentioned, left joining the other tables and not using some of the information is immediately going to destroy performance. – user1110302 May 08 '12 at 19:41