SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Evangelist
    Join Date
    Dec 2004
    Posts
    419
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Randomly selecting 2 lists of words from a single table

    Hi,

    I have a table containing different words, each is assigned to a category.

    e.g.

    Code:
    fld_Name    | catID
    ----------------------
    cheese      | 1
    water       | 1
    bread       | 1
    dog         | 2
    cat         | 2
    horse       | 2
    trousers    | 3
    hat         | 3
    jumper      | 3
    Users can pick from a category, and have the words randomly combined.

    e.g.

    SQL1:
    Code:
    SELECT fld_Name FROM words WHERE catID = 1 ORDER BY RAND() LIMIT 3
    SQL2
    Code:
    SELECT fld_Name FROM words WHERE catID = 2 ORDER BY RAND() LIMIT 3
    Then I put the results in an array and link the words from the 2 record sets together to get random combinations.

    I wondered though, if it would be possible to do this using one single SQL statement?

    I tried this:

    Code:
    SELECT DISTINCT v1. fld_Name,
                    v2.fld_Name
    FROM   (SELECT fld_Name
            FROM   words
            WHERE  catID = 1
            ORDER  BY RAND()
            LIMIT  3) v1,
           (SELECT fld_Name
            FROM   words
            WHERE  catID = 2
            ORDER  BY RAND()
            LIMIT  3) v2;
    But that doesn't really work, because it just generates output like this:

    Code:
    fld_Name  | fld_Name
    ----------------------
    water     | horse	
    bread     | horse	
    cheese    | horse	
    water     | dog	
    bread     | dog	
    cheese    | dog	
    water     | cat	
    bread     | cat	
    cheese    | cat
    Whereas I'd like to get something like this:

    Code:
    fld_Name  | fld_Name
    ----------------------
    water     | horse
    bread     | cat
    cheese    | dog
    But of course it doesn't do that - for each record in the "v2" table it loops through all of the records in the "v1" table...

    I'm not sure this can be done, or I don't have the skills to do it, so was interested to see if anyone had any ideas.

    I could just stick with the way I'm doing it now - e.g. using a scripting language to handle the output of the SQLs, but was just curious to see if there is a better way.

    Apologies for my mistakes etc.

    Any advice much appreciated.

    Thanks

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    You could use a UNION, so the query will return 6 rows. You'll still have to handle the rest in your scripting code.


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
  •