MySql - Case -Retrieve data

Kavi suja

Hi,
I have two tables 'contactinfo' contains 3 fields-- key,ContactModeList_pkey,value,whosinfo_pkey.
'whosinfo' contains fields-- pkey,hisPkey.Here 'contactinfo' table contains 2 rows for the same 'pkey' values.(The 'contactinfo' table contains two data for single whosinfo_pkey)

select whos.hisPkey ,

IF(ContInfo.ContactModeList_Pkey = 1, value, '') 'Mobile',

IF(ContInfo.ContactModeList_Pkey = 2, value, '') 'Email'

from whosinfo whos,contactinfo ContInfo where whos.pkey=ContInfo.whosInfo_pkey

and whos.pkey = '397';

The data in table 'contactinfo' as 422,1,8708708700,397 in next row 423,2,8708708700,397.
The data in table 'whosinfo' as 397,401.
The ouput will be as following:

hisPkey	Mobile	Email
401	8708708700
401		[email protected]

But I want to generate the output in single line as follows

hisPkey	Mobile	Email
401	8708708700	[email protected]

Can any one help me to do this?

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Iftikar Hussain13y ago
Accepted answer
Hi,
Try like this

select whos.hisPkey,
(select value from contactinfo where whosinfo_pkey= '397' and ContactModeList_pkey=1) as Mobile,
(select value from contactinfo where whosinfo_pkey= '397' and ContactModeList_pkey=2) as Email
from whosinfo whos where whos.pkey = '397';

Regards,
Iftikar
Iftikar Hussain13y ago
you're welcome

Regards,
Iftikar
Kavi suja13y ago
Hi Iftikar,
Thank you for your reply.It's working for me.
Renish Patel13y ago
Hey Kavitha,

You have to used GROUP_CONCAT() function in MY SQL to solve your problem..

Here i share link for you,which will explain you every things which you want..

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

If you get your answerd then make this post as answered..

Happy Coding..

Thanks

Renish Ankola