Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2002
    Location
    US
    Posts
    133

    Advanced MySQL help needed

    Ok i have a database full of username | word and i
    need a query, that searchs the db, and spits out the userid where it has 3 of the same words, or more
    The closest I have gotten is "SELECT s1.userid FROM word_db as s1, word_db as s2, word_db as s3 WHERE s1.userid=s2.userid=s3.userid AND s1.word=s2.word=s3.word"

  2. #2
    Join Date
    Jan 2004
    Location
    Quebec
    Posts
    164
    Can you post your table schema, cause right now I can't see where you want to go with this...

    Thanks

    Matt

  3. #3
    Join Date
    Sep 2002
    Location
    US
    Posts
    133
    UserID | Word
    UserID | Word
    UserID | Word

    i just want to search the Database and if there is a userID with 3 words that are the same, then do something..

  4. #4
    Join Date
    Jan 2003
    Posts
    1,715
    That approach may work, although you will need to stick with x=y pairs...
    SELECT s1.userid FROM word_db as s1, word_db as s2, word_db as s3 WHERE s1.userid = s2.userid AND s1.word = s2.word
    AND s1.userid = s3.userid AND s1.word = s3.word

    You can also use grouping functions for this.
    SELECT userid, word, count(*) as cnt FROM word_db
    GROUP BY userid, word HAVING cnt >= 3
    Game Servers are the next hot market!
    Slim margins, heavy support, fickle customers, and moronic suppliers!
    Start your own today!

  5. #5
    Join Date
    Jan 2004
    Location
    Quebec
    Posts
    164
    UserID must be a unique field no ?

    How can you have 3 word that match the UserID with that schema ?

    Cheers,

    Matt

  6. #6
    Join Date
    Sep 2002
    Location
    US
    Posts
    133
    UserID is a users Username. so if the user has 3 words all the same then blah.

  7. #7
    Join Date
    Mar 2004
    Location
    USA
    Posts
    4,342
    I didnt get the table scheme yet>>>

    There is a userid => is it unique, can there be two or more userids the same?

    the idea is to find a userid which has three words that are the same??? e.g.:

    aziz2598 | theman
    aziz2598 | theman
    aziz2598 | theman ???

    Peace,

  8. #8
    Join Date
    Jan 2003
    Posts
    1,715
    That is my understanding, yes. Although non-unique rows are an indication of dubious database design, SQL does not require unique rows.
    Game Servers are the next hot market!
    Slim margins, heavy support, fickle customers, and moronic suppliers!
    Start your own today!

  9. #9
    Join Date
    Mar 2004
    Location
    USA
    Posts
    4,342
    does he has the username and is before hand, or should it look regardeslessy?

Posting Permissions

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