Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2007
    Posts
    2

    Unanswered: Problem selecting records using column_name with %

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2007
    Posts
    2
    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

  4. #4
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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

  5. #5
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •