Select multiple columns in MySQL

Question

I need to combine two columns into one returning table from information_schema. So, I have multiple tables with fields. In every table there are some administrative field ( like, id, lastmodify, etc.) and others ( what I need ) starting with 'F_'.

SELECT TABLE_NAME, COLUMN_NAME FROM information_schema.columns WHERE TABLE_SCHEMA = "wyro" AND COLUMN_NAME LIKE "F_%" AND TABLE_NAME LIKE "D_%"

Everything is fine, if there is field like 'F_%' but in case these fields are missing, the table is not in the result of the query. I tried with several way using left / right join but having it on the same table does not bring to much result.

I need an output like:

| TABLE_NAME | COLUMN_NAME |
| D_blabla   | F_blabla    |
| D_blabla2  | F_blabla2   |
| D_blabla3  |             |
| D_blabla4  | F_blabla    |

O. Jones · Answer 1 · 2013-11-28T03:28:20.477

You're going to need to left-join two result sets to get what you want.

The first is

SELECT DISTINCT TABLE_NAME
  FROM information_schema.columns 
 WHERE TABLE_SCHEMA = 'wyro'
   AND TABLE_NAME LIKE 'D\_%'

The second is this:

SELECT TABLE_NAME, COLUMN_NAME
  FROM information_schema.columns 
 WHERE TABLE_SCHEMA = 'wyro' 
   AND COLUMN_NAME LIKE 'F\_%' 
   AND TABLE_NAME LIKE 'D\_%'

You join them like this to get your desired result set.

SELECT a.TABLE_NAME, b.COLUMN_NAME
  FROM (
      SELECT DISTINCT TABLE_NAME
        FROM information_schema.columns 
       WHERE TABLE_SCHEMA = 'wyro' 
         AND TABLE_NAME LIKE 'D\_%'
       ) AS a
  LEFT JOIN (
      SELECT TABLE_NAME, COLUMN_NAME
        FROM information_schema.columns 
       WHERE TABLE_SCHEMA = 'wyro'
         AND COLUMN_NAME LIKE 'F\_%' 
         AND TABLE_NAME LIKE 'D\_%'
       ) AS b ON a.TABLE_NAME = b.TABLE_NAME

This is so complex basically because you have two separately specified sets of results to combine. The first set is all the tables with names starting in "D_", and the second is all the columns you need.

Edit: escaped LIKE 'F\_%' and similar.

this answer is correct but it returns table names like `DOM` because the `_` is telling mysql to match for one character, you might want to use something like `TABLE_NAME LIKE "D|_%" ESCAPE '|'"` found in this answer here http://stackoverflow.com/questions/5020130/how-to-escape-literal-percent-sign-when-no-backslash-escapes-option-is-enabled — Tin Tran, Nov 27 '13 at 23:38
Right, good point! Thanks. I will change the code. @Kisauto, notice the change! — O. Jones, Nov 28 '13 at 03:25

Arnold Daniels · Answer 2 · 2013-11-27T22:52:06.043

SELECT
  TABLE_NAME,
  GROUP_CONCAT(IF(COLUMN_NAME LIKE "F_%", COLUMN_NAME, NULL)) AS COLUMN_NAME 
FROM information_schema.columns
WHERE TABLE_SCHEMA = "wyro" AND TABLE_NAME LIKE "D_%"
GROUP BY TABLE_NAME

The GROUP_CONCAT combined with the IF causes all columns starting with 'F_' to be combined as COLUMN_NAME. If that is only one, this approach works.

If there are multiple fields starting with 'F_' in a table they are concatenated like "F_abc, F_boo, F_bar".

score 0 · Answer 3 · answered Nov 27 '13 at 22:50

0

This can be done through CASE statement, no need of Joins

SELECT TABLE_NAME, case when COLUMN_NAME LIKE "F_%" THEN COLUMN_NAME ELSE '' END AS COLUMN_NAME
FROM information_schema.columns 
WHERE TABLE_SCHEMA = "wyro" 
AND TABLE_NAME LIKE "D_%"

answered Nov 27 '13 at 22:50

Santhosh

1,771
1
15
25

Tin Tran · Answer 4 · 2013-11-29T22:21:34.533

SELECT T.TABLE_NAME,
       IFNULL(C.COLUMN_NAME,'') AS COLUMN_NAME
FROM
 (SELECT TABLE_NAME 
    FROM information_schema.tables 
   WHERE TABLE_NAME LIKE 'D|_%' ESCAPE '|'
     AND TABLE_SCHEMA = 'wyro'
  )as T
 LEFT JOIN
 (SELECT TABLE_NAME,COLUMN_NAME
    FROM information_schema.columns
   WHERE TABLE_NAME LIKE 'D|_%' ESCAPE '|'
     AND COLUMN_NAME LIKE 'F|_%' ESCAPE '|'
     AND TABLE_SCHEMA = 'wyro'
  )as C
 USING (TABLE_NAME);

tested in this sqlfiddle
not sure why it takes forever for sqlfiddle to query from information_schema.columns or information_schema.tables though

Select multiple columns in MySQL

4 Answers4