My table name is held as data in another table... 1

[WuLegs]

Can anyone help?

I have a table in my database called "item". This table holds data that is common to all items in my database and has a primary key of 'id'. One of the columns in the "item" table is 'itemtype' which tells me which table I need to go to for the remaining data for that item (i.e. the item specific data as opposed to the common stuff in "item"). Presently I do two SQL SELECT queries. The first is:

SELECT * FROM item WHERE id = [id]

The second query uses the value of the 'itemtype' column from the first query for the table name, for example:

SELECT * FROM client WHERE id = [id]

where "client" has been derived from the first query. This second query is contructed using a string and the whole lot is done in Java.

This works. The problem is I can't help thinking it's inefficient and that there must be a way to join the two queries together so I only get one I/O hit on the database. I have examined JOINS, UNIONS, and Dynamic SQL all with no success. What I'd really like is to be able to run something like the following:

SELECT * FROM item AS a, (SELECT * FROM a.itemtype WHERE id = [id]) WHERE a.id = [id];

The problem is I don't really know what I'm doing! If anyone can help with this I would be eternally grateful!

 

[r937]

can you redesign the tables? sounds like a single table would be easier

meanwhile, you could LEFT OUTER JOIN the item table with all of its related tables, which works because the id value in the item table will match the id value in only one of them



r937.com | rudy.ca

 

[WuLegs]

Problem is that I don't know what tables there will be in existence (in addition to "item") as these are created dynamically too! Furthermore, the columns between the tables will not be consistent so merging into one isn't an option either.

As the database may ultimately extend to many tables with millions of items, I think JOINs may be too high an overhead?

I think I may stick with the two seperate queries for now and attempt to prepare some statements later in my development. Thanks for your help though.