balancing male and female in random

[dotJoon]

data in myTable7

(id) sex name
(1)  0   Mary 
(2)  1   Tom 
(3)  1   John 
(4)  0   Judy 
(5)  0   Jane 
(6)  1   Jack 
(7)  1   Andy 
(8)  1   Tim 
(9)  0   Liz 
(10) 0   Andrea 
(11) 1   Mikey 
(12) 0   Judy 
(13) 0   Anna

data in myTable8 

myGroup id
(1)     8
(1)     5
(2)     1
(2)     7
(2)     11
(3)     9
(3)     13
(4)     3
(4)     2
(4)     10
(4)     12

I have data in myTables like the above.

The code below produces the result below.

code

select name, myGroup
from myTable8
left join myTable7 on myTable7.id=myTable8.id
order by myGroup

result

(1) Tim
(1) Jane
(2) Mikey
(2) Mary
(2) Andy
(3) Anna
(3) Liz
(4) Judy
(4) Tom
(4) John
(4) Andrea

I like to produce only one randomly from each myGroup.
The following code is for it and the following result is one of the results.

code

(select name, myGroup
from myTable8
left join myTable7 on myTable7.id=myTable8.id
where myGroup=1
order by rand() limit 1)
union
(select name, myGroup
from myTable8
left join myTable7 on myTable7.id=myTable8.id
where myGroup=2
order by rand() limit 1)
union
(select name, myGroup
from myTable8
left join myTable7 on myTable7.id=myTable8.id
where myGroup=3
order by rand() limit 1)
union
(select name, myGroup
from myTable8
left join myTable7 on myTable7.id=myTable8.id
where myGroup=4
order by rand() limit 1)
union

order by myGroup

one of the results

(1) Tim
(2) Mary
(3) Anna
(4) John

The one of the results has a problem, i.e it doesn't balance the male and female.

It often produces 3 females and one male like the below.

(1) Jane
(2) Mary
(3) Anna
(4) Tom

It often produces 3 males and one female like the below.

(1) Tim
(2) Andy
(3) Liz
(4) Tom

Futhermore, it sometimes produces all females like the below.

(1) Jane
(2) Mary
(3) Anna
(4) Andrea


I like to make it that the result always has balancing the number of male and the number of female, i.e. two females and one males randomly.

[Aleksejs]

Would it be very bad if result were just one row? I assume you have just 4 groups because of four UNIONs.

SELECT t1.name, t2.name, t3.name, t4.name
FROM myTable7 AS t1, myTable7 AS t2, myTable7 AS t3, myTable7 AS t4,
myTable8 AS g1, myTable8 AS g2, myTable8 AS g3, myTable8 AS g4
WHERE g1.myGroup = 1 AND g2.myGroup = 2 AND g3.myGroup = 3 AND g4.myGroup = 4
AND g1.id = t1.id AND g2.id = t2.id AND g3.id = t3.id AND g4.id = t4.id AND (t1.sex + t2.sex + t3.sex + t4.sex = 2) ORDER BY RAND() LIMIT 1

[dotJoon]

Would it be very bad if result were just one row?

I need 4 rows because it has 4 myGroups.

The code below has no SQL errors.

code

(SELECT t1.name, t2.name, t3.name, t4.name

FROM 
myTable7 AS t1, myTable7 AS t2, myTable7 AS t3, myTable7 AS t4,myTable8 AS g1, myTable8 AS g2, 
myTable8 AS g3, myTable8 AS g4

WHERE 
g1.myGroup = 1 AND g2.myGroup = 2 AND g3.myGroup = 3 AND 
g4.myGroup = 4 AND g1.id = t1.id AND g2.id = t2.id AND g3.id = t3.id 
AND g4.id = t4.id AND (t1.sex + t2.sex + t3.sex + t4.sex = 2) 
ORDER BY RAND() LIMIT 1)

union

(SELECT t1.name, t2.name, t3.name, t4.name

FROM 
myTable7 AS t1, myTable7 AS t2, myTable7 AS t3, myTable7 AS t4,myTable8 AS g1, myTable8 AS g2, 
myTable8 AS g3, myTable8 AS g4

WHERE 
g1.myGroup = 1 AND g2.myGroup = 2 AND g3.myGroup = 3 AND 
g4.myGroup = 4 AND g1.id = t1.id AND g2.id = t2.id AND g3.id = t3.id 
AND g4.id = t4.id AND (t1.sex + t2.sex + t3.sex + t4.sex = 2) 
ORDER BY RAND() LIMIT 1)

result1

Tim
Jane

result2

Jane
Tim

result3

Tim
Tim

result4

Jane
Jane

The result1 and the result2 is Okay, but the result3 and the result4 is not okay because it has two males or two females, futuermore it produces two same names.

[Aleksejs]

Well, if you use just first column from the query I wrote it will always return result from group one. My question was would you settle for four columns instead of four rows.

[dotJoon]

Well, if you use just first column from the query I wrote it will always return result from group one. My question was would you settle for four columns instead of four rows.

I need four names which is randomly come from 4 myGroups each balancing male and female.

[dotJoon]

is it impossible?
(I am using mySQL 5)

[r937]

the question is not whether it is possible, but whether it should be done with SQL at all

there is an old joke, joon, perhaps you may not have heard it...

a man goes to the doctor and says "doc, it hurts when i do this"

and the doctor replied "so, don't do that then"

[dotJoon]

the question is not whether it is possible, but whether it should be done with SQL at all

Why might it NOT should be done with SQL?

Because is it too complex to execute ( in performance)?

there is an old joke, joon, perhaps you may not have heard it...

a man goes to the doctor and says "doc, it hurts when i do this"

and the doctor replied "so, don't do that then"

I think I understand the joke and what you want to say to me.

[dotJoon]

the question is not whether it is possible, but whether it should be done with SQL at all

Why might it NOT should be done with SQL?

Because is the random mating too sexually out of order?

( Actually it is from Yes or No questions instead of sexual matching.

I need to balance Yes and No question, two yes-questions and two no- questions. )

[r937]

run separate queries

[dotJoon]

run separate queries

do you mean another query While a query turns?

[r937]

do you mean another query While a query turns?

no, not nested queries

i meant two queries, one after the other

the first would get 2 random males, and the next would get 1 random female

simple, yes?

[dotJoon]

no, not nested queries

i meant two queries, one after the other

the first would get 2 random males, and the next would get 1 random female

simple, yes?

I've got what I want based on your idea. Thank you.