How to display field if entry exists in join table?

Question

I'm writing a query that display data from our issues table. We have another table called labels and a join table called issues_labels. We usually assign an issue a label of 'High-Priority', 'Medium-Priority' or 'Low-Priority.

I'm unsure how to write my query so it would return this result:

Id         | Title                  | Priority
2            everything is broken     Low-Priority
4            internets is down        High-Priority

I write queries all the time, but the simplicity (or not) of this one is driving me nuts. Do I need to write 3 sub-queries to pull issues that are linked to each label as so:

with hp_issues as (
SELECT *
FROM issues
INNER JOIN issues_labels on issues_labels.issue_id = issue.id
WHERE issues_labels.label_id = 10 --id for high priority issue
)
....

Any help appreciated.

Answer 1

Assuming issues_labels is a table that connects issues and labels in a many-to-many situation, you could do:

select
  i.id,
  i.title,
  l.priority
from issues i
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 any reason if one of your issues has more than one priority and you wanted it to be published as

5 | Some title | High Priority, Low Priority

you can do:

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

This is similar to MySQL's group_concat()

Example for that is here: http://sqlfiddle.com/#!15/3dce4/2

Answer 2

Can an issue only have one label? If so, I think the many-to-many is not needed.

However, you should be able to do something like:

SELECT i.issueID, i.title, l.priority
FROM issue AS i
    LEFT JOIN issue_label as il on i.issueID = il.issueID
    LEFT JOIN label as l on l.labelID = il.labelID
WHERE issue_label = 10;