Dynamically choose the JOIN table?

[roopurt18]

Someone in IRC asked about dynamically choosing which table to JOIN on.  I did a small amount of research and came up with this:

 

http://www.paste2.org/p/19928

/* #1 */
SELECT 1 AS `foo`, t.`id` FROM `wv_user`
INNER JOIN `byr_surveys` t ON t.`id`=1
WHERE 1
LIMIT 1
;

/* #2 */
SELECT 1 AS `foo`, t.`id` FROM `wv_user`
INNER JOIN `byr_surveys_response` t ON t.`id`=1
WHERE 1
LIMIT 1
;

/* THE MAGIC FOLLOWS */

/* now determine which table to use dynamically */
SET @myStmt := CONCAT(
  'SELECT 1 AS `foo`, t.`id` FROM `wv_user` ',
  'INNER JOIN ',
  IF( NULL IS NULL, '`byr_surveys`', '`byr_surveys_response`' ),
  ' t ON t.`id`=1 WHERE 1 LIMIT 1'
  );

/* prepare the statement */
PREPARE tmpStmt FROM @myStmt;

/* execute the statement */
EXECUTE tmpStmt;

DEALLOCATE PREPARE tmpStmt;

 

#1 and #2 are just examples.  When I run the significant statements in phpMyAdmin on the EXECUTE portion it says it found records but it won't return them; this is with or without the DEALLOCATE statement.  I'm hoping one of the better DB guys could offer something up for accessing that record set?

[Barand]

does this help

 

http://www.phpfreaks.com/forums/index.php/topic,105741.msg423465.html#msg423465

[roopurt18]

Possibly.  I was hoping for a way to do it without creating a procedure.

[fenway]

Possibly.  I was hoping for a way to do it without creating a procedure.

Impossible... certain things simply have to be strings literals (like the values passed to LIMIT clauses).  It's annoying, but that's how it is.  At least table names make sense.

[roopurt18]

I was looking over my old posts and when this one came up I was hit with a brainstorm.

 

I changed the dynamically built query to CREATE TEMPORARY TABLE `tmp_dynamic` SELECT ... and added a SELECT * FROM `tmp_dynamic` as my final query.  It seemed to work although I didn't play with it very long.

 

Anyways, it seems like a long, over-blown way to accomplish something that might be better left as application logic or a stored procedure anyways.

[fenway]

I was looking over my old posts and when this one came up I was hit with a brainstorm.

 

I changed the dynamically built query to CREATE TEMPORARY TABLE `tmp_dynamic` SELECT ... and added a SELECT * FROM `tmp_dynamic` as my final query.  It seemed to work although I didn't play with it very long.

 

Anyways, it seems like a long, over-blown way to accomplish something that might be better left as application logic or a stored procedure anyways.

Doesn't that just move the problem to naming the temp table? I'm confused.

[roopurt18]

I don't think so.  The original problem would have been something like this:

 

One table stores a master-list of something:

master_records
id, name, type

 

The items in the master_records table can be joined with any number of other tables depending on the `type` column.  It's assumed these tables all follow the same layout structure.

/* When type='SURVEY' */
survey_detail
id, master_id, ...

/* When type='ORDER' */
order_detail
id, master_id, ...

 

To pull from the tables:

SELECT * FROM `master_records` m INNER JOIN `survey_detail` d ON m.`id`=d.`master_id`;

SELECT * FROM `master_records` m INNER JOIN `order_detail` d ON m.`id`=d.`master_id`;

 

Both queries are the same, the only thing that changes is the join table.  So it makes sense to take out the part that varies in terms of maintenance and debugging.

 

But if the query built in the CONCAT function selects into a temporary table, I can then select from that temporary table as the last step.  The name of the temporary table is irrelevant.

 

/* now determine which table to use dynamically */
SET @myStmt := CONCAT(
  'CREATE TEMPORARY TABLE `tmp_whatever` ',
  'SELECT 1 AS `foo`, t.`id` FROM `wv_user` ',
  'INNER JOIN ',
  IF( NULL IS NULL, '`byr_surveys`', '`byr_surveys_response`' ),
  ' t ON t.`id`=1 WHERE 1 LIMIT 1'
  );

/* prepare the statement */
PREPARE tmpStmt FROM @myStmt;

/* execute the statement */
EXECUTE tmpStmt;

DEALLOCATE PREPARE tmpStmt;

SELECT * FROM `tmp_whatever`;

 

This originally came up in IRC; I've never actually had a need to do anything like this.  I don't particularly like this approach anyways; it feels so wrong.  Also, I'm fairly proficient in MySQL but there are definitely features I'm unaware of, so maybe there's a best way to accomplish this same thing already.

[fenway]

Well, I still consider that a "procedure" (although it's technically a prepared statement)... which I why I didn't consider it.