Solved

an sql query that needs a dynamic table name

Posted on 2008-10-22
14
324 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
  • 6
  • 2
  • 2
  • +1
14 Comments
 
LVL 59

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 59

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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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 59

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 59

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 59

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 59

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

773 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