MySQL: Why does where clause in subselect on result of another subselect not work?

Question

I have this select statement:

select A.id,
    (select id from B order by rand() limit 1)b1,
    (select id from B where not id in(b1) order by rand() limit 1)b2,
    (select id from B where not id in(b1,b2) order by rand() limit 1)b3,
    (select id from B where not id in(b1,b2,b3) order by rand() limit 1)b4,
    (select id from B where not id in(b1,b2,b3,b4) order by rand() limit 1)b5
from A

It's not very fast, it doesn't give me an error, but also doesn't do what I want.

I want to read 5 random id's from Table B and connect them to Table A.

So far so good, i get a result with 5 id's from Table B, BUT there are doubles.

Even though i have this where clause that should prevent doubles, i get them.

For example A.id:1 has b1=1, b2=6, b3=1, b4=9, B5=3

I would understand if MySQL throws an error because it can't handle the statement, but there is nothing, so I think it should work, but it doesn't.

Anyone has an answer to this?

Edit: It doesn't matter if the result looks like this(subquery): 1:2,7,3,9,6 or like this(join): 1:2 1:7 1:3 1:9 1:6

As long as every A.id has different B.id's. It's ok for two or more A.Id's to have the same B.id's, but it should be coincidental.

Still the question why MySQL accepts the query and gives a wrong result.

Vasil Nikolov · Accepted Answer · 2013-12-10T14:25:48.440

2

select id, b1, b2, b3, b4, b5
from (
    select A.id,
        @ := (select GROUP_CONCAT(DISTINCT id ORDER BY RAND()) AS ids from B),

        SUBSTRING_INDEX(SUBSTRING_INDEX(@, ',', 1), ',', -1) b1,
        SUBSTRING_INDEX(SUBSTRING_INDEX(@, ',', 2), ',', -1) b2,
        SUBSTRING_INDEX(SUBSTRING_INDEX(@, ',', 3), ',', -1) b3,
        SUBSTRING_INDEX(SUBSTRING_INDEX(@, ',', 4), ',', -1) b4,
        SUBSTRING_INDEX(SUBSTRING_INDEX(@, ',', 5), ',', -1) b5
    from A
) t

Example: http://sqlfiddle.com/#!2/d7df9/9

edited Dec 10 '13 at 14:25

answered Dec 10 '13 at 13:53

Vasil Nikolov

1,112
10
17

Wouldn't that result in every A.id having the same 5 B.Id's? – Martin Weber Dec 10 '13 at 14:03
well unique is a bold word in sql. i edited my text above. It's ok if two or more A.id's have the same B.id's, but it should be coincidental. it also doesn't matter if it's one row(subselect/substring) or 5(join) – Martin Weber Dec 10 '13 at 14:07

score 0 · Answer 2 · edited May 23 '17 at 12:13

0

Please check this post for selecting random rows: How to randomly select rows in SQL? You can then join those with the other table

SELECT B.id
FROM B JOIN A ON B.id=A.id
ORDER BY RAND()
LIMIT 5

edited May 23 '17 at 12:13

Community

1
1

answered Dec 10 '13 at 13:52

luckylwk

225
1
8

1. You won't have columns b1, b2, b3, b4, b5 in your result. 2. Where do you see that B.Id = A.id in question ? They don't seem to be related (which is strange, but... why not). – Raphaël Althaus Dec 10 '13 at 13:55
Thank you, i already read that. Problem is, that every A.id gets connected to the same five B.id's. I need 5 different B.Id's for each A.id it's ok if two A.id have the same five B.Id's, but it should be coincidental. – Martin Weber Dec 10 '13 at 13:56
Sorry I misunderstood you question. I thought you used connected tables. – luckylwk Dec 10 '13 at 13:58
@raphael-althaus: I use this query to build a relation. A is like Soccer-Teams and B is like Sponsors. At start of the season i want every team to get 5 sponsor-offers – Martin Weber Dec 10 '13 at 14:04
@MartinWeber The question is : is there a relation (before this query) between A and B, or not ? – Raphaël Althaus Dec 10 '13 at 14:07

MySQL: Why does where clause in subselect on result of another subselect not work?

2 Answers2