As an anonymous user, you can only add new data. If you would like to also modify existing data, please create an account and indicate your languages on your user page.

OmegaWiki/MySQL queries

From OmegaWiki
Jump to: navigation, search

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[edit]

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