referencing name of table from another table in sql query

Question

I have 3 tables.

1 main table - Component with columns - ID, name_of_component, ID_component

And 2 tables of components

first - Hardisk with columns - ID, capacity

second - RAM with columns - ID, speed

In main table I have ID of component, and details of it are in tables hardisk or RAM. Column name_of_component choses hardisk or RAM and ID_component choses the right details from that chosen table.

for example lets say in Main table is this row

1,RAM,2

and in RAM table are two rows

1,2GB
2,4GB

I tried something like this

Select * 
from (Select name_of_component 
      from Component 
      where ID=1) 
join Component on (Select name_of_component 
                   from Component 
                   where ID=1).ID = Component.ID_component

But it doesn't work. So is there any way, how to call information from table which name is selected from another table pls ? I cant use one table for details of RAMs and Graphic cards, because they have different details. So how can I do this ?

The syntax error is You entered an SQL statement that has an invalid FROM clause.

You have a syntax error right? You should add that. And decide which sql dialect you are using. mysql and sql-server are different and I don't believe your question is about how this query works in both engines... — rene, Apr 11 '15 at 10:13
Well i dont care if answer will be in MySQL or in Oracle, i just need an answer :)) and yep sorry i made wrong tags... i edited it... they are okay now — Mirek Mareš, Apr 11 '15 at 10:17

score 0 · Answer 1 · answered Apr 11 '15 at 10:21

0

You cannot do that in SQL itself. I suppose you could do a union:

select * from ram join component on ram.id=component.id where component.name_of_component='RAM' and component.id=myid
union 
select * from harddisk join component on ram.id=component.id where component.name_of_component='harddisk' and component.id=myid

Since name_of_component can only be one of these values at the same time, one part of the union will return nothing, and the other the proper part. But this can only work if the two tables have the same number of columns. If the two tables have a similar layout, maybe you don't need two tables.

answered Apr 11 '15 at 10:21

JP Moresmau

7,388
17
31

Yeah i thought about something like this, but i wanted to try it by way i described, because what if i will not have only 2 components ? But for example 100 ? Then will have to do this union for all of them ? :/ – Mirek Mareš Apr 11 '15 at 10:26
You can always do it from the host language calling the SQL: read the component first then build the SQL using the proper table name. You can also do it in stored procedures in some rdbms, but I think not in mysql (see http://stackoverflow.com/questions/190776/how-to-have-dynamic-sql-in-mysql-stored-procedure). – JP Moresmau Apr 11 '15 at 10:29
Or check for example http://stackoverflow.com/questions/20945882/how-to-write-a-mysql-function-with-dynamic-table-name for ways of doing it. – JP Moresmau Apr 11 '15 at 10:30

score 0 · Answer 2 · answered Apr 11 '15 at 11:31

You cannot do what you want with simple SQL for several reasons. The most obvious is that a SQL query returns a fixed set of columns, and these columns are defined when the code is written.

The way around this limitation is to use prepared statement, something like:

select @t = name_of_component
from MainTable
where id = 1;

select @sql = replace(replace('select * from @table where id = @i',
                              '@i', 1
                             ), '@table', @t);

prepare stmt from @sql;
execute stmt;

An alternative method is to use left join:

select mt.id, r.ram, hd.speed
from MainTable mt left join
     RAM r
     on mt.id = r.id left join
     HardDisk hd
     on mt.id = hd.id;

The value that you want will be in the appropriate column. You can put this in a view.

You might find that an EAV (entity-attribute-value) structure does a better job of representing this data for this purpose. Your main table would have the component, the id, and the type. An second table would list the features of all components, with rows like:

id   attribute     value
1    'RAM'         '2GB'
2    'RAM'         '4GB'
3    'Speed'       '7kRPM'

referencing name of table from another table in sql query

2 Answers2