MySQL use certain columns, based on other columns
- by Rabbott
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..
