?
Solved

an sql query that needs a dynamic table name

Posted on 2008-10-22
14
Medium Priority
?
349 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
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 2000 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

765 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