lundi 18 octobre 2021

SQLite Random sub query result

I have 2 SQLite tables, table_a and table_b. Table A contains a list of categories and Table B contains a list of words.

I'm trying to get all categories from table_a and a random word from table_b for each category. The below query allows me to get 1 result per category that has the lowest id in table_b.

    SELECT table_a.category_id, table_a.category, subQuery.word, subQuery.word_category, subQuery.word_id
    FROM table_a,
    (SELECT *
      FROM table_b
      GROUP BY category_id
      HAVING MIN(word_id)
    ) subQuery
WHERE subQuery.category_id = table_a.category_id;

Is there a way to return 1 random result from table_b for each category in table_a instead of the value with the lowest id?

I'm not really having much luck finding an answer online. Any help would be appreciated.




Aucun commentaire:

Enregistrer un commentaire