There is a Postgres database that contains the following related tables with the following fields:

  • therms (with integer type id (primary key) and text type value fields (contains words from indexed files))

  • fs_entries (with fields id type integer and value type text , where value contains the names of the files to be indexed)

  • therms_occurs (with integer and integer type file_id fields, where therm_id foreign key and is associated with the id field of the therms table, and file_id foreign key and is associated with the id field of the fs_entries table).

Help make a query (because I'm new to SQL), issuing all the words, sorted by file name, and the file names themselves.

    1 answer 1

     SELECT a.value, c.value FROM therms a JOIN therms_occurs b ON b.threm_id = a.id JOIN fs_entries c ON b.file_id = c.id ORDER BY c.value, a.value
    • a typo crept in, it is necessary "ON b.therm_id = a.id" - uilenspiegel