select table name to join from SQL query

Question

In current database design, I have a main table called "Leads" and some other tables starting with product_ ( product_life_insurance, product_medical_insurance , ... )

-Leads Table :
--ID
--Product
...

-Product_Life_insurance Table :
--ID
--LeadID
...

a Lead Row :

ID 5
Product: life_insurance

a product_life_insurance Row:

ID 1
LeadId 5

..

Is there anyway to create a query to select table Name from Leads and add "product_" prefix to it and then join it to product table ?

I mean :

SELECT *
FROM `leads` JOIN `product_life_insurance` ON `leads`.`id` = `product_life_insurance`.`leadID`
WHERE `leads`.`id` = '5';

I want to select table name for join from leads table and add "product_" prefix to it and use it in my query.

Thanks :)

You need to use dynamic SQL or construct the table name in your application. Having different tables with the same structure is usually a sign of poor database design -- at the very least, it impedes writing queries. — Gordon Linoff
– Gordon Linoff, Commented Mar 13, 2015 at 12:14
@CricFavor Thanks for pointing me to right direction , Iv'e learnt a new thing from you today mate :) now i'm able to do it with : SELECT * , CONCAT( 'product_', product ) AS product_table FROM leads` WHERE leads.id = '1000'` — PayamB
– PayamB, Commented Mar 13, 2015 at 12:21
@GordonLinoff problem is that those tables don't have same structure but they share a LeadID field , in this case CONCAT does the job for me :) Thanks — PayamB
– PayamB, Commented Mar 13, 2015 at 12:22
By the way , do you think is it going to be better if i store all data in one single table, like what WP does for postmeta and options ? Considering that i need to get a lot of reports out of this data, and we add normally 200 new records everyday. i know thats de normalization, but which one would be more logical to do ? — PayamB
– PayamB, Commented Mar 13, 2015 at 12:31

3 revs · Accepted Answer · 2015-03-13 14:11:48Z

1

You asked:

Is there any way to create a query to select table Name from Leads and add the "product_" prefix to it and then join it to product table ?

The answer is no. In pure SQL, you can't make variables of table names and then use them in your queries. You can, of course, do this in your host php code. But you'll need to use one query to fetch the table names, and more queries to fetch the results from that table name.

You can also use Dynamic SQL. That's a MySQL feature allowing you to create the text of SQL queries dynamically in the MySQL server, and then run those queries.

It sounds to me like you're trying to store several classes of entities (life insurance, annuities, vehicle insurance, others) having widely differing attributes.

This presents you with some schema-design options.

Should you use different tables (as you are doing), showing the entity (lead) class (life insurance) in a master table, and joining the particular table you need?
Should you try to coerce all the attributes into a single entity, leaving NULL or blank the attributes that are irrelevant for a particular class of entity?
Should you use a key/value store for your entities, the way WordPress's wp_postmeta table does?

Option 3 has a disadvantage if you do a lot of searching on attribute values: it requires your attributes to all be stored with the same data type. That data type is probably varchar(n). That means that it's hard to search on ranges of numeric attribute values. For example '10' is BETWEEN '1' AND '9' considered as text, but that's nonsense numerically. You can beat that problem using implicit typecasting, but that defeats the use of an index. That is,

 0+meta_value BETWEEN 0 AND 9

forces the comparison to work numerically on the meta_value column. It works, but not fast. That being said, Option 3 is the most flexible by far; you can add new attributes without changing table definitions.

A combination of Option 2 and Option 3, putting the most commonly searched attribute values into your main lead table, will probably yield the most robust solution.

Option 1 -- your present solution -- is a performance nightmare waiting to attack you when you can least afford it: as your application is scaling up.

NOTE: if you are using the MariaDB fork of MySQL your key_value table can contain a persistent, indexed, virtual column. For example,

meta_key   VARCHAR(255),
meta_value VARCHAR(255), 
meta_value_int BIGINT(20) AS (0+meta_value) PERSISTENT,
meta_value_float FLOAT AS (CAST(meta_value AS DECIMAL(30,10))) PERSISTENT

You can then index those virtual (defined with AS) columns and search them fast. meta_value columns that don't start with numbers will have the value 0 in the virtual columns.

edited Mar 13, 2015 at 14:11

community wiki

3 revs
Ollie Jones

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

3 Comments

PayamB Over a year ago

Yes you are right, It looks like i can't use an alias in the ON clause

PayamB Over a year ago

By the way do you have any comment for regarding my last comment i posted under my question ? Thanks

PayamB Over a year ago

Thanks for your complete answer Ollie, I really appreciate it.I decided to go with third approach and since i'm using MariaDB i will benefit from virtual columns.

Collectives™ on Stack Overflow

select table name to join from SQL query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest