table names on the fly

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

table names on the fly

Post 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.
User avatar
andre_c
Forum Contributor
Posts: 412
Joined: Sun Feb 29, 2004 6:49 pm
Location: Salt Lake City, Utah

Post by andre_c »

i would probably join all tables
User avatar
speedpacket
Forum Newbie
Posts: 4
Joined: Sun Jul 03, 2005 4:18 am

Post 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 :)
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post by lostboy »

two queries? one to get the ID value and the next to do the proper join to get the data
Post Reply