Databases
StackOverflow2013
Postsdbo
POMySQL use certain columns, based on other columns
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL use certain columns, based on other columns
    text
    copied!<p>I have this query:</p> <pre><code>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 </code></pre> <p>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:</p> <pre><code>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 </code></pre> <p>which works fine, the problem is, I'm using rails, and using STI for the xml_documents table. The <strong>articles.source</strong> that is provided to the <em>ExtractValue</em> 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'</p> <p>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..</p> <p>It would be nice if one could use a ternary operator, but i don't think this is possible..</p> <p><strong>EDIT</strong> At this Point I am looking at making a temp table, or simply using UNION to place multiple result sets together..</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload