Wordnet Query To Return Example Sentences

I have a use case where I have a word and I need to know the following things: Synonyms for the word (just the synonyms are sufficient) All senses of the word, where each sense co

Solution 1:

You can get the sentences from the samples table. E.g:

SELECT sample FROM samples WHERE synsetid =201062889;

yields:

The painting of Mary carries motherly love

His voice carried a lot of anger

So you could extend your query as follows:

SELECT 
    a.lemma AS `word`,
    c.definition,
    c.pos AS `part of speech`,
    d.sample AS `example sentence`,
    (SELECT 
            GROUP_CONCAT(a1.lemma)
        FROM
            words a1
                INNER JOIN
            senses b1 ON a1.wordid = b1.wordid
        WHERE
            b1.synsetid = b.synsetid
                AND a1.lemma <> a.lemma
        GROUPBY b.synsetid) AS `synonyms`
FROM
    words a
        INNER JOIN
    senses b ON a.wordid = b.wordid
        INNER JOIN
    synsets c ON b.synsetid = c.synsetid
        INNER JOIN
    samples d ON b.synsetid = d.synsetid
WHERE
    a.lemma = 'carry'ORDERBY a.lemma , c.definition , d.sample;

Note: The subselect with a GROUP_CONCAT returns the synonyms of each sense as a comma-separated list in a single row in order to cut down on the number of rows. You could consider returning these in a separate query (or as part of this query but with everything else duplicated) if preferred.

UPDATE If you really need synonyms as rows in the results, the following will do it but I wouldn't recommend it: Synonyms and example sentences both pertain to a particular definition so the set of synonyms will be duplicated for each example sentence. E.g. if there are 4 example sentences and 5 synonyms for a particular definition, the results would have 4 x 5 = 20 rows just for that definition.

SELECT 
    a.lemma AS `word`,
    c.definition,
    c.pos AS `part of speech`,
    d.sample AS `example sentence`,
    subq.lemma AS `synonym`
FROM
    words a
        INNER JOIN
    senses b ON a.wordid = b.wordid
        INNER JOIN
    synsets c ON b.synsetid = c.synsetid
        INNER JOIN
    samples d ON b.synsetid = d.synsetid
        LEFT JOIN
    (SELECT 
        a1.lemma, b1.synsetid
    FROM
        senses b1
    INNER JOIN words a1 ON a1.wordid = b1.wordid) subq ON subq.synsetid = b.synsetid
        AND subq.lemma <> a.lemma
WHERE
    a.lemma = 'carry'ORDERBY a.lemma , c.definition , d.sample;