While using mysql select query i need the get the column value as Yes/No instead of getting 0/1

Question

Column name is modified and the type is tinyint(1). How to fetch the column value as Yes/No using select query. And also I have 3 document name in one package while using select query the document name is split into three rows with same package name.

Here is the example for that column value has split into three rows: Expected Output:

name | data

abc | a,b,c def | be

Actual Output:

name | data
abc | a
abc | b
abc | c
def | be

Here is the code: select p.filename,p.version,doc.name from certificate_packages cp join packages p on cp.package_id=p.id left join package_documents pd on p.id=pd.package_id left join documents doc on doc.id=pd.document_id where cp.certificate_id=1099;

[![Attached the screenshot][1]][1]

you can write a stored procedure for this...which uses if else statement — Atul Agrawal, Apr 28 '17 at 06:13
i think your question title and body of question not match . — JYoThI, Apr 28 '17 at 06:13
https://stackoverflow.com/questions/24246345/is-there-a-way-to-display-yes-or-no-in-a-result-query-insted-of-0-or-1 (btw, you could find it yourself) — Michael, Apr 28 '17 at 06:16
Possible duplicate of [MySQL: Split comma separated list into multiple rows](http://stackoverflow.com/questions/3936088/mysql-split-comma-separated-list-into-multiple-rows) — JYoThI, Apr 28 '17 at 06:18
@JYoThI no, he wants to print `a,b,c` if there is some condition and `be` otherwise — Michael, Apr 28 '17 at 06:19
what you tried so far where your struggling ? show us some code . — JYoThI, Apr 28 '17 at 06:27
@JYoThI Here is the code that the column values are splitted as three rows: — Priya, Apr 28 '17 at 06:38
@JYoThI While giving group concat, it combining all the column values into one single row. Please find above the screenshot attached. — Priya, Apr 28 '17 at 06:50
try this select p.filename,p.version,GORUP_CONCAT(doc.name) AS NAME from certificate_packages cp join packages p on cp.package_id=p.id left join package_documents pd on p.id=pd.package_id left join documents doc on doc.id=pd.document_id where cp.certificate_id=1099 gorup by p.filename; — JYoThI, Apr 28 '17 at 06:52
@JYoThI Some name is missing, please find both screenshot before groupby file name and after groupby filename — Priya, Apr 28 '17 at 06:58
add group by version too like this gorup by p.filename,p.version — JYoThI, Apr 28 '17 at 07:00
@JYoThI Same output what i get for after groupby filename result. — Priya, Apr 28 '17 at 07:03
try this select p.filename,p.version,GORUP_CONCAT(doc.name) AS NAME from certificate_packages cp join packages p on cp.package_id=p.id left join package_documents pd on p.id=pd.package_id left join documents doc on doc.id=pd.document_id where cp.certificate_id=1099 p.filename,p.version ; — JYoThI, Apr 28 '17 at 07:04
i seen your screen shot your missed group_concat on that query — JYoThI, Apr 28 '17 at 07:04
@JYoThI I added now. Thanks for the solution. finally got it. Thank you so much — Priya, Apr 28 '17 at 07:06
i posted it as answer . mark it with green tick if it is useful . because it's useful for future user reference @Priya — JYoThI, Apr 28 '17 at 07:08

JYoThI · Accepted Answer · 2017-04-28T08:05:51.247

0

As per our comment the final solution is .You need give gorup_by p.filename,p.version ; column name like this

select p.filename,p.version,GROUP_CONCAT(doc.name) AS name 
from certificate_packages cp 
join packages p 
on cp.package_id=p.id 
left join package_documents pd 
on p.id=pd.package_id 
left join documents doc 
on doc.id=pd.document_id 
where cp.certificate_id=1099 
p.filename,p.version ;

edited Apr 28 '17 at 08:05

answered Apr 28 '17 at 07:09

JYoThI

11,977
1
11
26

While using mysql select query i need the get the column value as Yes/No instead of getting 0/1

1 Answers1