MySQL use certain columns, based on other columns

Question

I have this query:

SELECT COUNT(articles.id) AS count 
FROM articles, xml_documents, streams 
WHERE articles.xml_document_id = xml_documents.id 
AND xml_documents.stream_id = streams.id
AND articles.published_at BETWEEN '2010-01-01' AND '2010-04-01'
AND streams.brand_id = 7

Which just uses the default equajoin by specifying three tables in csv format in the FROM clause.. What I need to do is group this by a value found within articles.source (raw xml).. so it could turn into this:

SELECT COUNT(articles.id) AS count, ExtractValue(articles.source, "/article/media_type") AS media_type
FROM articles, xml_documents, streams 
WHERE articles.xml_document_id = xml_documents.id 
AND xml_documents.stream_id = streams.id
AND articles.published_at BETWEEN '2010-01-01' AND '2010-04-01'
AND streams.brand_id = 7
GROUP BY media_type

which works fine, the problem is, I'm using rails, and using STI for the xml_documents table. The articles.source that is provided to the ExtractValue method will be of a couple different formats.. So what I need to be able to do is use "/article/media_type" IF xml_documents.type = 'source one' and use "/article/source" if xml_documents.type = 'source two'

This is just because the two document types format their XML differently, but I don't want to have to run multiple queries to retrieve this information..

It would be nice if one could use a ternary operator, but i don't think this is possible..

EDIT At this Point I am looking at making a temp table, or simply using UNION to place multiple result sets together..

Answer 1

I would support the previous poster:

SELECT
    COUNT(articles.id) AS count,
    (CASE xml_documents.type
        WHEN 'source one' THEN ExtractValue(articles.source, '/article/media_type')
        WHEN 'source two' then ExtractValue(articles.source, '/article/source')
        ELSE NULL
    END) as media_type,
FROM ...