MySQL concatenate based on field content

Question

I have a MySQL table with a structure like the following:

MySQL fields

I'm looking for a select statement which will concatenate the last four fields as follows:

ID    Source
1     Search engine
2     Email
3     Coupon mailout
4     Relative of owner

I can think of a way to do this by using subqueries to choose the relevant fields to concatenate based on Type, nested subqueries to select values that are not NULL or 'Other', followed by JOINs, CONCAT()s and a UNION, but that seems a very complicated approach to the problem.

Am I overthinking this? Is there a simpler way?

geertjanvdk · Accepted Answer · 2012-06-27 12:31:45Z

1

If you can't fix your data, you can simply use IF() function. In my example, I'm using simple column names to make it a bit clearer:

mysql> SELECT IF(c1 IS NOT NULL AND c1 != 'Other', c1,
        IF(c2 IS NOT NULL AND c2 != 'Other', c2,
        IF(c3 IS NOT NULL AND c3 != 'Other', c3, c4))) FROM t1;

The above was tested.

You could make a Stored Function out of it if you need to use it often.

But, if you can, fix the table structure?

answered Jun 27, 2012 at 12:31

geertjanvdk

3,52827 silver badges27 bronze badges

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

1 Comment

stuwest Over a year ago

Thanks! I'd love to fix the structure, but sadly I'm stuck with it for the time being.

Question Mark · Accepted Answer · 2012-06-27 12:29:02Z

0

I reckon you'll need a few IFs and IFNULLs in there, something like:

SELECT ID, IF(Type='Online', IFNULL(OnlineSourceOther, OnlineSource), IFNULL(OfflineSourceOther, OfflineSource)) AS Source FROM referrals

Totally untested, YMMV, good luck, let us know how it goes.

answered Jun 27, 2012 at 12:29

Question Mark

3,6261 gold badge27 silver badges30 bronze badges

Collectives™ on Stack Overflow

MySQL concatenate based on field content

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related