Solved

an sql query that needs a dynamic table name

Posted on 2008-10-22
14
345 Views
Last Modified: 2013-12-07
hello,
i hv a query like this ----select * from (select table_name from tbllinker where rank=5) where name='tudop'. select table_name from tbllinker where rank=5-->this query will return table name based on rank provided and based on this table name i will fetch data.but this query is not running in mysql.any ideas?
thnx
0
Comment
Question by:mithunda5011
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
  • 2
  • +1
14 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22779934
In SQL Server, I would build a dynamic query like this:

DECLARE @table_name nvarchar(50), @sql nvarchar(100)

SELECT @table_name = table_name
FROM tbllinker WHERE rank = 5

SET @sql = 'SELECT * FROM [' + @table_name + '] WHERE name = ''tudop'''
EXEC(@sql)

I will see if I can find the equivalent for MySQL, but figured that would help give you the jist of what to do.
0
 
LVL 9

Expert Comment

by:jamesgu
ID: 22779981
I don't think you can use a dynamic table name is a sql statement,

you may use dynamic sql statement for the whole string
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22779982
Alright, I know this should work:

$table_name = "yourtable";
SELECT * FROM $table_name;

However, give this a try to incorporate your query.
SELECT $table_name = table_name
FROM tbllinker WHERE rank = 5;
 
SELECT * FROM $table_name WHERE name='tudop';

Open in new window

0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 9

Expert Comment

by:jamesgu
ID: 22779991
agree with mwvisa1
0
 
LVL 5

Expert Comment

by:JBart_17
ID: 22779993
your subselect does not return a field named "name" only a field named "table_name"
Select *
from
     (Select table_name
       from tbllinker
       where rank = 5)
where table_name = 'tudop'
0
 
LVL 5

Expert Comment

by:JBart_17
ID: 22780018
wow, I'm slow by the time I posted, 4 other post magically appeared :-)
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22780040
Which is what my example is - Dynamic SQL.

Here is probably a better example of how to construct and execute dynamic SQL query in MySQL:
http://datacharmer.blogspot.com/2005/10/power-of-dynamic-queries.html
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22780060
Think my first example only works from PHP.  The blog post seems to be more fitting as Jamesqu pointed out, this is not possible from normal SQL statement.
0
 

Author Comment

by:mithunda5011
ID: 22780351
so far the very fisrt post looks nearest ...but i will give it a try on mysql :)
0
 

Author Comment

by:mithunda5011
ID: 22780396
i think i need to create a stored proc with 2 input params--one is the rank and other is the name...and then try something like mwvisa1' first post...can anybody give me that store proc...in mysql?
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 22780947
http:#22780040

See the link in the above post.  The author has an example of a stored procedure that calls dynamic query.  The syntax is:

set @query = 'dynamic query statement';
prepare table_list from @query;
execute table_list;
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 23338343
Thought my first post and http:#22780040 answered the question.  mithunda5011, confirmed that I thought.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question