Page 1 of 1

table names on the fly

Posted: Tue Jun 21, 2005 9:59 am
by magicrobotmonkey
I have an interesting problem brought on by poor db design. (not my design!!)

I have one table that contains a bunch of records. Each one of those records has a table associated with it. If there was a record ID 10 in the first table, there would be a table called rec10, and so on for all the records in table one. What I need to do is pull a field from the rec* table. Is there a way to change which table is being joined on a line to line basis?

I feel like the solution to this is on the tip of my tongue. I think I might need some more coffee.

Posted: Thu Jun 23, 2005 1:03 am
by andre_c
i would probably join all tables

Posted: Sun Jul 03, 2005 4:43 am
by speedpacket
What about

Code: Select all

SELECT *
FROM Table t
INNER JOIN Rec10
  ON t.primaryKey = Rec10.foreignKey
WHERE t.recordID = Rec10

UNION

SELECT *
FROM Table t
INNER JOIN Rec20
  ON t.primaryKey = Rec20.foreignKey
WHERE t.recordID = Rec20

UNION 

...
This requires that all RecXX tables have the same data structure though... (you might need to select actual fields as well in stead of select *), and you will of course need to loop through your records in Table first to generate this query in php or something...

Either way it's doable, but as you already said, bad database design :)

Posted: Mon Jul 04, 2005 11:16 pm
by lostboy
two queries? one to get the ID value and the next to do the proper join to get the data