Convert columns into rows with inner join in mysql

Question

Please take a look at this fiddle.

I'm working on a search filter select box and I want to insert the field names of a table as rows.

Here's the table schemea:

CREATE TABLE general
    (`ID` int, `letter` varchar(21), `double-letters` varchar(21))
;

INSERT INTO general
    (`ID`,`letter`,`double-letters`)
VALUES
    (1, 'A','BB'),
    (2, 'A','CC'),
    (3, 'C','BB'),
    (4, 'D','DD'),
    (5, 'D','EE'),
    (6, 'F','TT'),
    (7, 'G','UU'),
    (8, 'G','ZZ'),
    (9, 'I','UU')
;


CREATE TABLE options
    (`ID` int, `options` varchar(15))
;

INSERT INTO options
    (`ID`,`options`)
VALUES
    (1, 'letter'), 
    (2, 'double-letters')
;

The ID field in options table acts as a foreign key, and I want to get an output like the following and insert into a new table:

 id   field   value
 1     1       A
 2     1       C
 3     1       D
 4     1       F
 5     1       G
 6     1       I
 7     2       BB
 8     2       CC
 9     2       DD
 10    2       EE
 11    2       TT
 12    2       UU
 13    2       ZZ

My failed attempt:

select DISTINCT(a.letter),'letter' AS field
from general a
INNER JOIN
options b ON b.options = field
union all
select DISTINCT(a.double-letters), 'double-letters' AS field
from general a
INNER JOIN
options b ON b.options = field

I want to fetch the distinct values of each field and output it in JSON format for a jQuery plugin. But I also need the field names (letter , double-letter in this case) of the fetched values as well for the input name attributes for use in html form submit. — RedGiant
– RedGiant, Commented Aug 2, 2014 at 20:43
I understand (I think) it was just confusing because you were joining on a column alias (which you can't do). I think the answer I posted will work for you. — Brian DeMilia
– Brian DeMilia, Commented Aug 2, 2014 at 20:45

Brian DeMilia · Accepted Answer · 2014-08-02 20:42:02Z

1

Pretty sure you want this:

select distinct a.letter, 'letter' AS field
  from general a
 cross JOIN options b
 where b.options = 'letter'
union all
select distinct a.`double-letters`, 'double-letters' AS field
  from general a
 cross JOIN options b
 where b.options = 'double-letters'

Fiddle: http://sqlfiddle.com/#!2/bbf0b/18/0

A couple to things to point out, you can't join on a column alias. Because that column you're aliasing is a literal that you're selecting you can specify that literal as criteria in the WHERE clause.

You're not really joining on anything between GENERAL and OPTIONS, so what you really want is a CROSS JOIN; the criteria that you're putting into the ON clause actually belongs in the WHERE clause.

answered Aug 2, 2014 at 20:42

Brian DeMilia

13.3k1 gold badge25 silver badges37 bronze badges

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

Comments

Bertrand Ring · Accepted Answer · 2014-08-02 21:50:10Z

1

I just made this query on Oracle. It works and produces the output you described :

SELECT ID, CASE WHEN LENGTH(VALUE)=2THEN 2 ELSE 1 END AS FIELD, VALUE  
FROM (
SELECT rownum AS ID, letter AS VALUE FROM (SELECT DISTINCT letter FROM general ORDER BY letter)
UNION
SELECT (SELECT COUNT(DISTINCT LETTER) FROM general) +rownum AS ID, double_letters AS VALUE
 FROM (
    SELECT DISTINCT  double_letters FROM general ORDER BY double_letters)
)

It should also run on Mysql.

I did not used the options table. I do not understand his role. And for this example, and this type of output it seems unnecessary

Hope this could help you to.

answered Aug 2, 2014 at 21:50

Bertrand Ring

392 bronze badges

Collectives™ on Stack Overflow

Convert columns into rows with inner join in mysql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related