Current SQL query string:
"SELECT verses.book_name, verses.verse_id, verses.verse_category, verses.verse_testament, verses.verse_reference_chapter, verses.verse_content, verses.verse_enabled,
books.book_name, books.book_id, books.book_author, books.book_testament, books.book_summary, books.book_summary_clue, books.book_number
FROM verses
RIGHT JOIN books
ON verses.book_name = books.book_name
WHERE verses.verse_enabled = 1
ORDER BY rand() LIMIT 3";

A book may have many verses. The above query may return multiple different verses from the same book. This means the results may contain duplicated books, dont want this - book has to be distinct.

How would I still return all of the current column data from the tables, but only show distinct books in the 3 results?

I realise I dont need to list all those fields in the SELECT also, which ones can I take out?

Thanks

    Can you give an example of the results you are getting vs the results you would like to get?

      Current results (duplicated books i.e. Philippians (book 1 and 3)):
      Verse 1 = Philippians 2:3,4
      Book 1 = Philippians

      Verse 2 = Romans 3:23
      Book 2 = Romans

      Verse 3 = Philippians 4:4
      Book 3 = Philippians

      Desired results (no duplicated books i.e. book 1, 2 and 3 are all different):
      Verse 1 = Philippians 2:3,4
      Book 1 = Philippians

      Verse 2 = Romans 3:23
      Book 2 = Romans

      Verse 3 = Isaiah 53:6
      Book 3 = Isaiah

      Cheers

        If you are not trying to get multiple verses per book then you should be able to simply specify "SELECT DISTINCT verses.book_name" or perhaps use grouping "GROUP BY verses.book_name ORDER BY rand() LIMIT 3"

          My SQL is terrible, should have realised that. DISTINCT did not work but "GROUP BY verses.book_name" did.

          Problem is now it only ever gets the first Philippians verse - 2:3,4. There are 3 different verses in the verses table for this book in the books table.

          Maybe I need another SELECT in the JOIN?

            I thought you might have that problem since it's basically randomizing the same results. Someone here is prob better with SQL than me but if you don't have a solution by tomorrow I think I can do something using UNION.

              I would try brad's suggestion first but let me know what happens when you run this (I stripped out all of the other variables) 😉 No union just subquery

              SELECT book_name, verse_id FROM (
              SELECT books.book_name, verses.verse_id FROM verses RIGHT JOIN books ON verses.book_name = books.book_name ORDER BY rand()
              ) temp GROUP BY book_name LIMIT 3;

                I could not get the GROUP_CONCAT() working so I tried the subquery:

                SELECT * FROM (
                SELECT books.book_name, verses.verse_id, verses.verse_category, verses.verse_testament, verses.verse_reference_chapter, verses.verse_content, verses.verse_enabled,
                books.book_id, books.book_author, books.book_testament, books.book_summary, books.book_summary_clue, books.book_number
                FROM verses
                RIGHT JOIN books
                ON verses.book_name = books.book_name
                WHERE verses.verse_enabled = 1
                ORDER BY rand())
                temp
                LIMIT 3
                GROUP BY book_name

                I get different verses within the Philippians book now (always the 3rd result). But the first two verses are always the same (2 tim 1:7 and Isaiah 53:6). These verses are also the only verses for their associated books.

                What does temp do?

                Cheers folks

                  Hmm... When I did my testing it worked perfectly. I setup two tables and this is my test query:

                  test1 = Id, name
                  test2 = Id, ident, verse

                  SELECT `name`, `verse` FROM (
                         SELECT test1.`name`, test2.`verse` FROM test1 RIGHT JOIN test2 ON test1.`Id` = test2.`ident` ORDER BY rand()
                  ) `temp` LIMIT 3 GROUP BY `name`;
                  

                  Your SQL doesn't look valid having LIMIT before GROUP BY

                  Perhaps there is an issue using SELECT * ??

                  When using sub queries you are creating a temporary table. This can be seen by adding "EXPLAIN" before any query... e.g. "EXPLAIN SELECT...."

                  I am no SQL expert, perhaps brad can add a GROUP_CONCAT example because I am here to learn too and I have never seen that used before 😉

                    "Your SQL doesn't look valid having LIMIT before GROUP BY"
                    Copy and pasted wrong 🙂

                    I tried not using * in the SELECT - same result.

                    GROUP_CONCAT might be the way forward.

                      So I am trying GROUP_CONCAT now within the query and the book fields are returned fine (no GROUP_CONCAT used). Verse fields are causing a problem.

                      SELECT GROUP_CONCAT(verses.book_name, verses.verse_id, verses.verse_category, verses.verse_testament, verses.verse_reference_chapter, verses.verse_content, verses.verse_enabled),
                      books.book_name, books.book_id, books.book_author, books.book_testament, books.book_summary, books.book_summary_clue, books.book_number
                      FROM verses

                      RIGHT JOIN books
                      ON verses.book_name = books.book_name
                      WHERE verses.verse_enabled = 1
                      GROUP BY verses.book_name
                      ORDER BY rand() LIMIT 3

                      The problem is maybe my PHP to get the groups of verse fields as each field throws up a "Notice: Undefined index" e.g. "Notice: Undefined index: verse_content".

                      Do I need to iterate through the grouped values with a loop instead of just:
                      $verse_category = $row_1['verse_category'];

                      Cheers

                        Write a Reply...