OmegaWiki/MySQL queries

From OmegaWiki

Often seeing some example queries and understanding what they do if you want to try things out yourself. Just put them into your window and try out what they get you :-)

[edit] Queries by Kim Brunings

SELECT collection_id as id, spelling 
FROM  uw_collection, uw_defined_meaning, uw_expression
WHERE collection_mid=defined_meaning_id
AND uw_defined_meaning.expression_id=uw_expression.expression_id

SELECT collection_id as id, spelling                                                                       
FROM  uw_collection, uw_defined_meaning, uw_expression
WHERE collection_mid=defined_meaning_id
AND uw_defined_meaning.expression_id=uw_expression.expression_id

SELECT uw_collection.collection_id AS id ,count(*) AS total
FROM  uw_collection_contents, uw_collection
WHERE uw_collection.collection_id=uw_collection_contents.collection_id
GROUP BY uw_collection.collection_id

SELECT spellings.id, spelling, counts.total FROM
       (
               SELECT collection_id as id, spelling 
               FROM  uw_collection_ns, uw_defined_meaning, uw_expression_ns
               WHERE collection_mid=defined_meaning_id
               AND uw_defined_meaning.expression_id=uw_expression_ns.expression_id
       ) AS spellings JOIN
       ( 
               SELECT uw_collection_ns.collection_id AS id ,count(*) AS total
               FROM  uw_collection_contents, uw_collection_ns
               WHERE uw_collection_ns.collection_id=uw_collection_contents.collection_id
               GROUP BY uw_collection_ns.collection_id
       ) AS counts
               ON spellings.id=counts.id