SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Dec 2006
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Select a random WORD from table1 while excluding several WORDS from table 2

    Hi,
    I have to query the db in the following scenario.

    I develop a little vocabulary learning application and have two tables. One with users and ids of words that they already know. The other table has all words in the db.

    I'm looking for a way to get a random word from the table WORDS for steve that is not one of the words he already knows.

    TABLE USERS

    user | id
    steve | 12
    steve | 14
    steve | 10
    lara | 10

    TABLE WORDS

    word | id
    fun | 7
    hard | 12
    cool | 10
    strong | 15

    It would be fantastic if anyone could help me with this one. Right now I first get all the words that Steve already knows and build a query ($protect). Then I use this query to get the random word.

    Code:
    "SELECT * FROM vocdb WHERE item = 'Vocabulary' AND status = 'Approved' $protect ORDER BY RAND() LIMIT 1"

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by kellerkind View Post
    I'm looking for a way to get a random word from the table WORDS for steve that is not one of the words he already knows.
    Code:
    SELECT word
      FROM words
     WHERE id NOT IN
           ( SELECT id 
               FROM users
              WHERE user = 'Steve' )
    ORDER
        BY RAND() LIMIT 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Dec 2006
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks so much r937!

    There are several ways to speed up the process instead of using ORDER BY RAND()
    http://akinas.com/pages/en/blog/mysql_random_row/

    But I don't see any way how I could use one of the solutions with my database. Do you also think it makes sense to first do one query to get all the ids that would be possible to select and then use php to pick a random id from the array and use a second query to get the information for this id?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by kellerkind View Post
    Do you also think it makes sense to first do one query to get all the ids that would be possible to select and then use php to pick a random id from the array and use a second query to get the information for this id?
    no

    the methods in that article do not involve retrieving all ids
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Dec 2006
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So they would work? Please let me know and I will try to figure it out again.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, of course those methods work

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Dec 2006
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hehe ok well I will have a go with it. thanks again!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •