0

Hi
I have a select statement that uses coalesce but I am wondering if there is a better way that it could be written??
I am trying to come up with a comparison table to show wich software has certain attributes.
Many thanks
Julie

select a.title AS SOFTWARE,
COALESCE((select 'Y'
from taxonomy_index b, taxonomy_term_data c
where c.tid = b.tid
and c.vid = 3
and b.tid = 17
and a.nid = b.nid), 'N') AS 'Results Recorded',
COALESCE((select 'Y'
from taxonomy_index b, taxonomy_term_data c
where c.tid = b.tid
and c.vid = 3
and b.tid = 13
and a.nid = b.nid), 'N') AS 'Free Trial',
COALESCE((select 'Y'
from taxonomy_index b, taxonomy_term_data c
where c.tid = b.tid
and c.vid = 3
and b.tid = 19
and a.nid = b.nid), 'N') AS 'Tutorials',
COALESCE((select 'Y'
from taxonomy_index b, taxonomy_term_data c
where c.tid = b.tid
and c.vid = 3
and b.tid = 15
and a.nid = b.nid), 'N') AS 'Personalise',
COALESCE((select 'Y'
from taxonomy_index b, taxonomy_term_data c
where c.tid = b.tid
and c.vid = 3
and b.tid = 16
and a.nid = b.nid), 'N') AS 'Advice',
COALESCE((select 'Y'
from taxonomy_index b, taxonomy_term_data c
where c.tid = b.tid
and c.vid = 3
and b.tid = 17
and a.nid = b.nid), 'N') AS 'Record Self'
from node a
where a.type = 'Software'
2
Contributors
1
Reply
2
Views
5 Years
Discussion Span
Last Post by smantscheff
0

You could create a temporary table which links the taxonomy_index.tids to the column labels and use it in a left join construction.
For further help post a test case, including CREATE TABLE and INSERT statements.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.