0

I have table v_c_country with column table_name

I can make an query and, as result, have the list of all tables from column table_names. Generally, it means. I have it as source to another query. But what in situation when the list of table_names are different than list of original exists tables f.ex. from information_schema.tables? (f.ex. we prepare info about tables witch will be create in the future).

How to make a query on table v_c_country with result a list of table_names but verified with f.ex. information_schema.tables? (list only with names of real existing tables) Example pls.

Andrew
  • 233
  • 1
  • 3
  • 10

2 Answers2

1
select c.table_name from v_c_country c
inner join INFORMATION_SCHEMA.TABLES i on i.TABLE_NAME = c.table_name
juergen d
  • 201,996
  • 37
  • 293
  • 362
  • thank you, but it don't work for me, err -> Fatal error: Call to a member function fetch_assoc() on a non-object. I try it few times ... – Andrew May 12 '12 at 15:11
  • @Andrew: That's odd. It works for me. See [this example](http://sqlfiddle.com/#!2/495bf/1). What is your exact query? – juergen d May 12 '12 at 15:17
  • And your error looks more like a PHP error message than a sql error. – juergen d May 12 '12 at 15:18
  • ok, I did make it very very simple query and this don't want to work. $STH_1 = $DBH_R->query(" select c.table_name from v_c_country c inner join INFORMATION_SCHEMA.TABLES i on i.TABLE_NAME = c.table_name "); while (($row_1 = $STH_1 -> fetch_assoc()) !== null){ echo $row_1['table_name']; echo "
    "; }     – Andrew May 12 '12 at 15:30
  • @Andrew: I am not so fit in PHP but shouldn't `fetch_assoc()) !== null)`be `fetch_assoc()) != null)`? There is a `=` too much in it. – juergen d May 12 '12 at 15:34
  • you are right but it's not a problem. As I see in your example - it is working. As you see in a simple query - not. Can it be a problem with a dbase version, my is 5.1 and your is 5.5.2? – Andrew May 12 '12 at 15:39
  • @Andrew: You could output the error message. See here [fatal-error-call-to-a-member-function-fetch-assoc-on-a-non-object](http://stackoverflow.com/questions/5121027/). I don't think it is the DB version. This table is available in MySQL 5.1 – juergen d May 12 '12 at 15:49
  • The error info -> Unknown column 'c.table_name' in 'field list' – Andrew May 12 '12 at 21:01
  • my mistake, i'm working with two dbases, local and in net - wrong connection, sorry :((( All is working ok, thank you for your help and patience. – Andrew May 12 '12 at 21:34
1

Here are two equivalent ways to do this:

select *
from v_c_country c join
     information_schema.tables t
     on c.table_name = t.table_name

or

select *
from v_c_country c
where c.table_name in (select table_name from information_schema.tables t)
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • thnks for your help, none of above work, the err is -> Fatal error: Call to a member function fetch_assoc() on a non-object. I try it few times ... – Andrew May 12 '12 at 15:05
  • sorry - of course all is working ok, my mistake, sorry and thank you for help – Andrew May 12 '12 at 21:35
  • No problem. You make yourself more popular if you accept answers and vote up responses. – Gordon Linoff May 12 '12 at 21:41
  • ech, I'm not looking for any popularity, I'm looking here for a real help from peple who have knowledge, as you, f.ex. But, of course, it's nice to receive a vote as a thanks for your help. Than I try to vote up responses, but very often comments do not contribute entries to correct the problem. Sometimes I think that people write without an idea for a solution to the problem. But sometimes the negative hints help think through the problem differently. – Andrew May 14 '12 at 11:31