MySQL join tables where table name is a field of another table

Question

I have 5 tables. One primary and 4 additional (they have different columns).

1. objects
2. obj_mobiles
3. obj_tablets
4. obj_computers

Here is the structure of my main table (objects).

ID | type | name | etc...

So what I want to do is to join objects with other (obj_mobiles,obj_tablets,...) tables, depending on type field. I know that I should use dynamic SQL. But I can't make procedure. I think it should look like something like this.

SELECT objects.type into @tbl FROM objects;
PREPARE stmnt FROM "SELECT * FROM objects AS object LEFT JOIN @tbl AS info ON object.id = info.obj_id"; 
EXECUTE stmnt;
DEALLOCATE PREPARE stmnt;

Aslo pseudo-code

SELECT * FROM objects LEFT JOIN [objects.type] ON ... 

Can anyone post procedure? Also I want to have all rows not just 1 row. Thanks.

Mazda

Answer 1

If you want all rows (bulk output) and not one row at a time, the below should be fast and also the output for all rows will contain all columns.

Lets consider below to be the fields of the tables. obj_mobiles - ID | M1 | M2 obj_tablets - ID | T1 | T2 obj_computers - ID | C1 | C2 objects - ID | type | name | etc.,

Select objects.*, typestable.*
from (
    select ID as oID, "mobile" as otype, M1, M2, NULL T1, NULL T2, NULL C1, NULL C2 from obj_mobiles 
    union all
    select ID as oID, "tablet" as otype, NULL, NULL, T1, T2, NULL, NULL from obj_tablets 
    union all
    select ID as oID, "computer" as otype, NULL, NULL, NULL, NULL, C1, C2 from obj_computers) as typestable 
        left join objects on typestable.oID = objects.ID and typestable.otype = objects.type;

+------+--------------------+----------+------+----------+------+------+------+------+------+------+
| ID   | name               | type     | ID   | type     | M1   | M2   | T1   | T2   | C1   | C2   |
+------+--------------------+----------+------+----------+------+------+------+------+------+------+
|    1 | Samsung Galaxy s2  | mobile   |    1 | mobile   |    1 | Thin | NULL | NULL | NULL | NULL |
|    2 | Samsung Galaxy Tab | tablet   |    2 | tablet   | NULL | NULL | 0.98 |   10 | NULL | NULL |
|    3 | Dell Inspiron      | computer |    3 | computer | NULL | NULL | NULL | NULL | 4.98 | 1000 |
+------+--------------------+----------+------+----------+------+------+------+------+------+------+

The table are create as below.

mysql> create table objects (ID int, name varchar(50), type varchar (15));
Query OK, 0 rows affected (0.05 sec)
mysql> insert into objects values (1, "Samsung Galaxy s2", "mobile"), (2, "Samsung Galaxy Tab", "tablet"), (3, "Dell Inspiron", "computer");
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0


mysql> create table obj_mobiles (ID int, M1 int, M2 varchar(10));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into obj_mobiles values (1, 0.98, "Thin");
Query OK, 1 row affected (0.00 sec)


mysql> create table obj_tablets (ID int, T1 float, T2 int(10));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into obj_tablets values (2, 0.98, 10);
Query OK, 1 row affected (0.00 sec)


mysql> create table obj_computers (ID int, C1 float, C2 int(10));
Query OK, 0 rows affected (0.03 sec)
insert into obj_computers values (3, 4.98, 1000);

also to confirm the datatypes of the columns are same as the original columns, the result is saved into a table and datatypes are checked below.

create table temp_result as
Select objects.*, typestable.*
from (
    select ID as oID, "mobile" as otype, M1, M2, NULL T1, NULL T2, NULL C1, NULL C2 from obj_mobiles 
    union all
    select ID as oID, "tablet" as otype, NULL, NULL, T1, T2, NULL, NULL from obj_tablets 
    union all
    select ID as oID, "computer" as otype, NULL, NULL, NULL, NULL, C1, C2 from obj_computers) as typestable 
        left join objects on typestable.oID = objects.ID and typestable.otype = objects.type;

mysql> desc temp_result;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
| type  | varchar(15) | YES  |     | NULL    |       |
| oID   | int(11)     | YES  |     | NULL    |       |
| otype | varchar(8)  | NO   |     |         |       |
| M1    | int(11)     | YES  |     | NULL    |       |
| M2    | varchar(10) | YES  |     | NULL    |       |
| T1    | float       | YES  |     | NULL    |       |
| T2    | int(11)     | YES  |     | NULL    |       |
| C1    | float       | YES  |     | NULL    |       |
| C2    | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
11 rows in set (0.00 sec)

Cressi

This will be faster than the solution by Devart – Cressi

Thanks. This is what I've looking for. – Mazda

Answer 2

If objects is a parent table it means that objects.ID is a unique object. Right? All other items (mobiles, tablets, computers) are child ones for object and mobile and tablet cannot have the same ID. If so, it is enough to use this simple query -

SELECT * FROM objects o
  LEFT JOIN obj_mobiles m
    ON o.id = m.ID
  LEFT JOIN obj_tablets t
    ON o.id = t.ID
  LEFT JOIN obj_computers c
    ON o.id = c.ID

Add WHERE clause to filter types, e.g.: WHERE o.type = 'mobile'.

Teryl

Check my solution for better performance query, with lesser joins. – Cressi

Answer 3

USE CONCAT to create your prepare statement. eg.

@sql = CONCAT('SELECT * FROM objects AS object LEFT JOIN ', @tbl, ' AS info ON object.id = info.obj_id');
PREPARE stmnt FROM @sql;
...

Holzer