sql - How to display field if entry exists in join table? -

i'm writing query display data our issues table. have table called labels , join table called issues_labels. assign issue label of 'high-priority', 'medium-priority' or 'low-priority.

i'm unsure how write query return result:

id         | title                  | priority 2            broken     low-priority 4            internets down        high-priority 

i write queries time, simplicity (or not) of 1 driving me nuts. need write 3 sub-queries pull issues linked each label so:

with hp_issues ( select * issues inner join issues_labels on issues_labels.issue_id = issue.id issues_labels.label_id = 10 --id high priority issue ) .... 

any appreciated.

assuming issues_labels table connects issues , labels in many-to-many situation, do:

select   i.id,   i.title,   l.priority issues left join issues_labels il on il.issues_id = i.id left join labels l on l.id = il.labels_id 

example: http://sqlfiddle.com/#!15/b78ee/1

for reason if 1 of issues has more 1 priority , wanted published

5 | title | high priority, low priority

you can do:

select   i.id,   i.title,   string_agg(l.priority, ',') issues left join issues_labels il on il.issues_id = i.id left join labels l on l.id = il.labels_id group   i.id,   i.title 

this similar mysql's group_concat()

example here: http://sqlfiddle.com/#!15/3dce4/2