I have two tables:
Table A of customer data
ID - Name - Age - etc
1 - Joe - 24
2 - Bloggs - 46
Table B is a temp table of random numbers
ID - RandomNumber
1 - 68
2 - 46
3 - 49
4 - 24
I need to select from table A
SELECT * FROM tableA a WHERE a.age = [randomNumber from tableB]
The where clause needs to look into tableB. Looks at first random number - 68, does this exist in Age column of tableA, no, so move to next random number. does 46 exist in tableB, yes, it then returns 2 - Bloggs - 46, and inserts that row into another table.
the process starts again but from where is left off - does 49 exist in tableA, no, so next, does 24 exist in tableA, yes, copy that row into other table.
i have a list of 150 students, and want to randomly select 30 by age.
I could use RAND() function but number will change every time, so id rather place random numbers in a table first, and then look up.
any help is much appreciated.