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 · Accepted Answer · 2013-11-28 03:28:20Z

1

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.

edited Nov 28, 2013 at 3:28

answered Nov 27, 2013 at 22:48

O. Jones

110k17 gold badges134 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tin Tran Over a year ago

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 stackoverflow.com/questions/5020130/…

O. Jones Over a year ago

Right, good point! Thanks. I will change the code. @Kisauto, notice the change!

Santhosh · Accepted Answer · 2013-11-27 22:50:15Z

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, 2013 at 22:50

Santhosh

1,8011 gold badge16 silver badges26 bronze badges

Comments

Arnold Daniels · Accepted Answer · 2013-11-27 22:52:06Z

0

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".

edited Nov 27, 2013 at 22:52

answered Nov 27, 2013 at 22:42

Arnold Daniels

16.6k6 gold badges55 silver badges84 bronze badges

Comments

Tin Tran · Accepted Answer · 2013-11-29 22:21:34Z

0

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

edited Nov 29, 2013 at 22:21

answered Nov 29, 2013 at 22:13

Tin Tran

6,1763 gold badges25 silver badges34 bronze badges

Collectives™ on Stack Overflow

Select multiple columns in MySQL

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related