CODEWITHSUNDEEP
sqldatabase
Jason Clark
Jason Clark

Reputation: 1425

Looping function

I have two tables:

Table A of customer data

ID -  Name     -   Age
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.

Upvotes: 0

Views: 71

Answers (3)

avi
avi

Reputation: 912

Here you can find an example for your request.

select top 30 * from TableA where Age in (select RandomNumber from TableB)

Upvotes: 1

Gawron
Gawron

Reputation: 85

You can try

SELECT tableA.name FROM tableA, tableB WHERE tableA.age IN (SELECT RandomNumber FROM tableB)

If you need only 30 students, you should also add

LIMIT 30

Upvotes: 1

A Hocevar
A Hocevar

Reputation: 726

If you want to randomly order the output rows, ORDER BY RAND() is your friend:

SELECT sub.* FROM (
  SELECT ta.ID,
    ta.Name,
    ta.Age,
    ROWNUM num
  FROM TABLEA ta
    JOIN TABLEB tb ON ta.Age = tb.RandomNumber
    ORDER BY RAND() ) sub
  WHERE sub.num <=30

This will retrieve maximum 30 randomly selected names from your table A

Note: if you are using a DBMS where LIMIT is implemented, you can simplify the query and simply use

  SELECT ta.ID,
    ta.Name,
    ta.Age
  FROM TABLEA ta
    JOIN TABLEB tb ON ta.Age = tb.RandomNumber
    ORDER BY RAND()
    LIMIT 30

Upvotes: 1

Related Questions