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.
table names on the fly
Moderator: General Moderators
-
magicrobotmonkey
- Forum Regular
- Posts: 888
- Joined: Sun Mar 21, 2004 1:09 pm
- Location: Cambridge, MA
- speedpacket
- Forum Newbie
- Posts: 4
- Joined: Sun Jul 03, 2005 4:18 am
What about
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
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
...Either way it's doable, but as you already said, bad database design