MySQL - Return columns based on value

Question

I need to retrieve data from a database and I have no way to change its structure. There are 3 distinct fields for address:

Personal: client_address_1, client_address_2, client_address_3, client_address_4

Postal: client_postaladdress_1, client_postaladdress_2, client_postaladdress_3, client_postaladdress_4

Company: client_company_address_1, client_company_address_2, client_company_address_3, client_company_address_4

and one field (client_prefered_address) which contains in which address the client wants to receive his correspondence.

From them I need to retrieve the address of choice, so, if the client is marked as postal, it should return the columns: client_postaladdress_1, client_postaladdress_2, client_postaladdress_3, client_postaladdress_4 but not the others.

Is there any way to do it? I have been Googling for two days.

Thanks

SQL:

CREATE TABLE IF NOT EXISTS `client` (
  `client_id` int(11) NOT NULL AUTO_INCREMENT,
  `client_address_1` varchar(255) COLLATE utf8_bin NOT NULL,
  `client_address_2` varchar(255) COLLATE utf8_bin NOT NULL,
  `client_address_3` varchar(255) COLLATE utf8_bin NOT NULL,
  `client_address_4` varchar(255) COLLATE utf8_bin NOT NULL,
  `client_postaladdress_1` varchar(255) COLLATE utf8_bin NOT NULL,
  `client_postaladdress_2` varchar(255) COLLATE utf8_bin NOT NULL,
  `client_postaladdress_3` varchar(255) COLLATE utf8_bin NOT NULL,
  `client_postaladdress_4` varchar(255) COLLATE utf8_bin NOT NULL,
  `client_company_address_1` varchar(255) COLLATE utf8_bin NOT NULL,
  `client_company_address_2` varchar(255) COLLATE utf8_bin NOT NULL,
  `client_company_address_3` varchar(255) COLLATE utf8_bin NOT NULL,
  `client_company_address_4` varchar(255) COLLATE utf8_bin NOT NULL,
  `client_prefered_address` char(10) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`client_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=4;

--
-- Dumping data for table `client`
--

INSERT INTO `client` (`client_id`, `client_address_1`, `client_address_2`, `client_address_3`, `client_address_4`, `client_postaladdress_1`, `client_postaladdress_2`, `client_postaladdress_3`, `client_postaladdress_4`, `client_company_address_1`, `client_company_address_2`, `client_company_address_3`, `client_company_address_4`, `client_prefered_address`) VALUES
(1, 'Yellow house', 'Yellow street, 25', '09090 Yellow city', 'Yellow Country', 'Blue postbox', 'Blue avenue, 90', '09039 Blue city', 'Blue Country', 'Green house', 'Green street, 100', '02930 Green city', 'Green Country', 'Postal'),
(2, 'Apple house', 'Apple street, 200', 'Apple State 2039', 'Apple Land', 'Melon House', 'Melon Boulevard ', 'Melon State ', 'Melon Land', '', '', '', '', 'Personal'),
(3, '', '', '', '', '', '', '', '', 'Chocolate Factory', 'Charlie street 293', 'Chocolate CH', 'Chocolate Kingdom ', 'Company');

Do you need to do this all in MySQL? Why not use whatever programming language you are using to do this? Get the preferred address in one query, then use another to get the right fields. It's possible in MySQL, but I think it'll be cleaner to run 2 queries. — gen_Eric
– gen_Eric, Commented Nov 7, 2013 at 16:18

M Khalid Junaid · Accepted Answer · 2013-11-07 16:25:55Z

1

Try this with CASE

select 

(CASE WHEN client_prefered_address ='Postal' 
THEN client_postaladdress_1 
WHEN client_prefered_address ='Personal' THEN client_address_1
WHEN client_prefered_address ='Company' THEN client_company_address_1
ELSE NULL END) `addressone`
,
(CASE WHEN client_prefered_address ='Postal' 
THEN client_postaladdress_2 
WHEN client_prefered_address ='Personal' THEN client_address_2
WHEN client_prefered_address ='Company' THEN client_company_address_2
ELSE NULL END) `addresstwo`,

(CASE WHEN client_prefered_address ='Postal' 
THEN client_postaladdress_3 
WHEN client_prefered_address ='Personal' THEN client_address_3
WHEN client_prefered_address ='Company' THEN client_company_address_3
ELSE NULL END) `addressthree`,

(CASE WHEN client_prefered_address ='Postal' 
THEN client_postaladdress_4 
WHEN client_prefered_address ='Personal' THEN client_address_4
WHEN client_prefered_address ='Company' THEN client_company_address_4
ELSE NULL END) `addressfour`

FROM `client`

FIDDLE

answered Nov 7, 2013 at 16:25

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

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

1 Comment

user2965516 Over a year ago

dianuj, you are the best!!! I found you on skype, I hope it its okay that I added you. Thank you

gen_Eric · Accepted Answer · 2013-11-07 16:25:42Z

1

I suggest using 2 queries to solve this. One to get the preferred address, and another to get the correct fields. Use whatever programming language you are using to generte the 2nd query with the right fields.

If you really want to use MySQL, you can use the CASE statement (it's like a switch):

SELECT client_id, (
  CASE client_prefered_address
  WHEN 'Postal' THEN client_postaladdress_1
  WHEN 'Personal' THEN client_address_1
  WHEN 'Company' THEN client_company_address_1
END) AS address_1, (
  CASE client_prefered_address
  WHEN 'Postal' THEN client_postaladdress_2
  WHEN 'Personal' THEN client_address_2
  WHEN 'Company' THEN client_company_address_2
END) AS address_2, (
  CASE client_prefered_address
  WHEN 'Postal' THEN client_postaladdress_3
  WHEN 'Personal' THEN client_address_3
  WHEN 'Company' THEN client_company_address_3
END) AS address_3, (
  CASE client_prefered_address
  WHEN 'Postal' THEN client_postaladdress_4
  WHEN 'Personal' THEN client_address_4
  WHEN 'Company' THEN client_company_address_4
END) AS address_4

FROM client

DEMO: http://sqlfiddle.com/#!2/eab6b/3

answered Nov 7, 2013 at 16:25

gen_Eric

228k42 gold badges304 silver badges343 bronze badges

1 Comment

gen_Eric Over a year ago

@dianuj: Sorry, I'm just a ninja is all :D

elixenide · Accepted Answer · 2013-11-07 16:20:10Z

Try something like this:

SELECT `client_id`,
    IF(`client_prefered_address` = 'personal', `client_address_1`, IF(`client_prefered_address` = 'postal', `client_postaladdress_1`, `client_company_address_1`) AS `address_1`,
    IF(`client_prefered_address` = 'personal', `client_address_2`, IF(`client_prefered_address` = 'postal', `client_postaladdress_2`, `client_company_address_2`) AS `address_2`,
    IF(`client_prefered_address` = 'personal', `client_address_3`, IF(`client_prefered_address` = 'postal', `client_postaladdress_3`, `client_company_address_3`) AS `address_3`,
    IF(`client_prefered_address` = 'personal', `client_address_4`, IF(`client_prefered_address` = 'postal', `client_postaladdress_4`, `client_company_address_4`) AS `address_4`
FROM `client`
WHERE blahblahblah

Collectives™ on Stack Overflow

MySQL - Return columns based on value

3 Answers 3

FIDDLE

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related