0

A fairly terribly named question, sorry!

So I have a table that gives me a listid number which relates to the end of a table name. How can I join to that table?

My query gives me rellistid and relsubscriberid

The relsubscriberid is in a table that is named oempro_subscribers_XXX where XXX is the rellistid

How can I search for that subscriberid in that table?

This isn't my database, this is a database for a web-based marketing system called Oempro and I've not been able to find a direct link in any other tables.

Schema:

LinkTrackID int(11) NO  PRI     auto_increment
RelOwnerUserID  int(11) NO  MUL     
RelCampaignID   int(11) NO  MUL     
RelEmailID  int(11) NO  MUL     
RelAutoResponderID  int(11) NO  MUL     
RelListID   int(11) NO  MUL     
RelSubscriberID int(11) NO          
LinkURL text    NO          
LinkTitle   varchar(250)    NO          
ClickDate   datetime    NO
franglais
  • 928
  • 2
  • 15
  • 39
  • 2
    share your table schema – Sid M Jul 02 '14 at 07:47
  • 1
    This sounds like a bad idea; you shouldn't have hundreds of separate subscribers tables, you should have one. Then your query becomes simple, you'd just need a WHERE clause. – Oliver Charlesworth Jul 02 '14 at 07:47
  • 1
    That's a very bad schema. You should have a single table `oempro_subscribers` with an additional column to contain the `XXX`. – jcaron Jul 02 '14 at 07:47
  • @jcaron Yes, yes it is a terrible schema. It's not my database, it's an application's database that we use... it's horrible and I hate it! – franglais Jul 02 '14 at 07:50
  • How many `oempro_subscribers_XXX` tables do you have? If the number is limited (and stable), you can use the solution I added in the comment to my answer. – jcaron Jul 02 '14 at 07:54
  • @jcaron unfortunately the system creates a new table for every list (which is one per marketing campaign) It's not pretty... – franglais Jul 02 '14 at 07:55
  • Then I only see the option of doing it through 2 separate SQL requests, with the second one "generated" based on the result of the first. – jcaron Jul 02 '14 at 07:56

3 Answers3

0

AFAIK, you can't do it in SQL. So you'll have to do the first query, then generate the second query using the results of the first (how exactly you do this depends on the language being used).

Note however that IMHO, that's a very bad schema. You should have a single table oempro_subscribers with an additional column to contain the XXX.

jcaron
  • 17,302
  • 6
  • 32
  • 46
  • Note that if the number of `oempro_subscribers_XXX` is limited, you can do: `SELECT 'XXX1' AS xxx,* FROM oempro_subscribers_xxx1 UNION ALL SELECT 'XXX2' AS www,* FROM oempro_subscribers_xxx2` etc. This requires the number, order and type of the columns of all such tables to match (or you'll have to explicitly list the columns). You can then join/select based on the new `xxx` column. You can also create a view for this to make things easier. – jcaron Jul 02 '14 at 07:53
0

Maybe you can use prepared statement, like described here: How To have Dynamic SQL in MySQL Stored Procedure

... but I really think you need to fix your schema.

Community
  • 1
  • 1
jon martin solaas
  • 459
  • 1
  • 4
  • 14
0

It sounds like you may need to use some kind of Dynamic SQL.

I don't think you'll be able to do it one step.

1) You can use the results of the first query to get the table name. 2) You can use that result to generate the query "on the fly" against the second table.

Check out the documentation.

http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html

Shaun Harvey
  • 21
  • 4