Problem selecting records using column_name with %

AQG · #1 (**permalink**) 08-07-07, 11:33

Hello, I'm new to MySQL and will appreciate any help

SELECT COLUMN_NAME FROM information_schema.`COLUMNS` C WHERE table_name='disk_ses_app1' AND COLUMN_NAME LIKE 'mntU%';

Gives me the column names from wich I need to obtain the records:
+------------------+
| COLUMN_NAME |
+------------------+
| mntU_1_var |
| mntu__var |
| mntU_1_var |
| mntu__var |
+------------------+
But I need to obtain all the records from the table using the Column_name and don't know where to go from here???

Any Ideas???
Thanks

r937 · #2 (**permalink**) 08-07-07, 11:44

you need to select the TABLE_SCHEMA as well as the COLUMN_NAME

looks like you have the same table in more than one database

AQG · #3 (**permalink**) 08-07-07, 11:48

Thanks,
But as you might see I'm new tho MySQL could you please give me an example, I'ved looked around and have found nothing.

Regards

aschk · #4 (**permalink**) 08-10-07, 05:43

You're going to need a stored procedure which contains a cursor which does a union between all the select column from tables

e.g.

Code:

CREATE PROCEDURE build_tables_results()
BEGIN
  DECLARE cur1 CURSOR FOR SELECT COLUMN_NAME,TABLE_NAME FROM information_schema.`COLUMNS` C WHERE table_name='disk_ses_app1' AND COLUMN_NAME LIKE 'mntU%';
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
  DECLARE a,b VARCHAR(255);

  OPEN cur1;
  REPEAT
    FETCH cur1 INTO a, b;
    SELECT a FROM b;
  UNTIL done END REPEAT;

  CLOSE cur1;
END

aschk · #5 (**permalink**) 08-10-07, 05:44

By the way, my answer above does NOT contain the UNION part for the resultset. I'm uncertain how to achieve this (without doing further work).

Internet Services

Web Development

Digital Marketing

Consumer Tech