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