How to merge SQL SELECT fields

Question

I had a MySQL Table with an ENUM. Since I had to change this ENUM sometimes, I transfered it to another table. Now I have to create a statement, which will give me the name of the enum/newTable in one select field.

Table 1 (bottle):

id    | size      | size_id
1     | small     | (NULL)
2     | medium    | (NULL)
3     | big       | (NULL)
4     | (NULL)    | 1
5     | (NULL)    | 2
6     | (NULL)    | 3

Table 2 (bottle_size):

id    | name
1     | small
2     | medium
3     | big

SELECT:

SELECT id, <dontknow> as size
FROM bottle b
LEFT JOIN bottle_size bz ON b.size_id = bz.id
WHERE size = 'small';

How could I do this?

Thanks

Are you familiar with the concept of normalization of databases? — Rachcha
– Rachcha, Commented Mar 21, 2013 at 10:30
Half of your data uses text values, and the other half joins to a new bottle_size table? You might want to fix that first, get a level playing field. — AjV Jsy
– AjV Jsy, Commented Mar 21, 2013 at 10:38
sure i am... this is only an example for a much bigger database with thousands of rows in each table.. i cant change the layout, so i have to deal with it. @Mr Fuzzy Button: it is not text, it is an enum.. and like i've said before: the enum was to inflexible, so we had to transfer it. — trialgod
– trialgod, Commented Mar 21, 2013 at 10:39
You could run an UPDATE to join all the rows correctly, then leave the bottle.size text field unused? — AjV Jsy
– AjV Jsy, Commented Mar 21, 2013 at 10:42
yes i could, but thats not my decision... i can't change the layout and datasets, i only need a right select. — trialgod
– trialgod, Commented Mar 21, 2013 at 10:44

Niko · Accepted Answer · 2013-03-21 11:03:13Z

2

You can use IFNULL() to select either the first field if it is not NULL or the second field otherwise. Because this results in a dynamic field ("size"), you need to use HAVING instead of WHERE to add a condition:

SELECT b.id, IFNULL(b.size, bz.name) AS size
FROM bottle b
LEFT JOIN bottle_size bz ON b.size_id = bz.id
HAVING size = 'small';

Demo: http://sqlfiddle.com/#!2/9e2df/3

edited Mar 21, 2013 at 11:03

answered Mar 21, 2013 at 10:52

Niko

26.8k9 gold badges97 silver badges112 bronze badges

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

Comments

AjV Jsy · Accepted Answer · 2013-03-21 11:06:03Z

0

Also available as a subquery :)

SELECT
  b.id,
  ifnull(b.size,
    (SELECT name FROM bottle_size bz WHERE bz.id=b.size_id)
        ) AS size_
FROM bottle b
HAVING size_ = 'small'

(http://sqlfiddle.com/#!2/9e2df/15)

answered Mar 21, 2013 at 11:06

AjV Jsy

6,1154 gold badges39 silver badges30 bronze badges

Collectives™ on Stack Overflow

How to merge SQL SELECT fields

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