OmegaWiki/MySQL queries

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 :-)

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, uw_defined_meaning, uw_expression
    WHERE collection_mid=defined_meaning_id
    AND uw_defined_meaning.expression_id=uw_expression.expression_id
) AS spellings

JOIN

( 
    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
) AS counts

ON spellings.id=counts.id