Randomly selecting 2 lists of words from a single table

[bumpyride]

Hi,

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

e.g.

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:

SELECT fld_Name FROM words WHERE catID = 1 ORDER BY RAND() LIMIT 3

SQL2

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:

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:

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:

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

[guido2004]

You could use a UNION, so the query will return 6 rows. You'll still have to handle the rest in your scripting code.