Merge two sub-queries row-to-row

Question

I'm trying to create a random name generator query using a set of test data from my database.

The field name stores a customers full name, I however would like the query to grab a random first name from the name field and a random last name from the name field.

Query:

select concat(first_name, ' ', last_name) from

((select lcase(SUBSTRING_INDEX(SUBSTRING_INDEX(name, ' ', 1), ' ', -1)) as first_name
from customers 
where SUBSTRING_INDEX(SUBSTRING_INDEX(name, ' ', 1), ' ', -1) not like '%Mrs'
and SUBSTRING_INDEX(SUBSTRING_INDEX(name, ' ', 1), ' ', -1) not like '%Mrs'
and SUBSTRING_INDEX(SUBSTRING_INDEX(name, ' ', 1), ' ', -1) not like '%Mr'
and SUBSTRING_INDEX(SUBSTRING_INDEX(name, ' ', 1), ' ', -1) not like '%.%'
and length(SUBSTRING_INDEX(SUBSTRING_INDEX(name, ' ', 1), ' ', -1)) > 1
order by rand() 
limit 10) as first_name_tbl,

(select lcase(SUBSTRING_INDEX(SUBSTRING_INDEX(name, ' ', 2), ' ', -1)) as last_name
from customers 
where SUBSTRING_INDEX(SUBSTRING_INDEX(name, ' ', 2), ' ', -1) not like '%Mrs'
and SUBSTRING_INDEX(SUBSTRING_INDEX(name, ' ', 2), ' ', -1) not like '%Mrs'
and SUBSTRING_INDEX(SUBSTRING_INDEX(name, ' ', 2), ' ', -1) not like '%Mr'
and SUBSTRING_INDEX(SUBSTRING_INDEX(name, ' ', 2), ' ', -1) not like '%.%'
and length(SUBSTRING_INDEX(SUBSTRING_INDEX(name, ' ', 2), ' ', -1)) > 1
order by rand() 
limit 10) as last_name_tbl);

The problem with my query is that it returns duplicate names and not the right number of records.

Current results:

100 rows in set

| sabrina mole                       |
| daniel mole                        |
| helen mole                         |
| jenny mole                         |
| caroline mole                      |
| catherine mole                     |
| julia mole                         |
| carmella mole                      |
| mark mole                          |
| catharine mole                     |
| sabrina salgado                    |
| daniel salgado                     |
| helen salgado                      |
| jenny salgado                      |
| caroline salgado                   |
| catherine salgado                  |
| julia salgado                      |
| carmella salgado                   |
.....

Desired results

10 rows in set

| sabrina mole                       |
| daniel salgado                     |
| helen oinn                         |
| jenny hird                         |
| caroline thompson                  |
| catherine helena                   |
| julia taylor                       |
| carmella spectrum                  |
| mark storrie                       |
| catharine pat                      |

When you say duplicate names, do you mean, you only want each first name and last name used once ? — Holmes IV, Jul 10 '15 at 18:32

score 3 · Accepted Answer · edited May 23 '17 at 12:29

3

The problem is you are creating a cross join with two 10 row tables.

So 10 x 10 = 100 rows.

You need use a session variable rowid to each table
See rowid on MySql

( SELECT @rowidFirst:=@rowidFirst+1 as rowid, first_name_tbl.*
  FROM  
    ( SELECT .... ) as first_name_tbl
) as firstWithRowID

( SELECT @rowidLast:=@rowidLast+1 as rowid, last_name_tbl.*
  FROM  
    ( SELECT .... ) as last_name_tbl
) as lastWithRowID

then join by row_id

SELECT *
FROM firstWithRowID, lastWithRowID
WHERE firstWithRowID.rowid = lastWithRowID.rowid

edited May 23 '17 at 12:29

Community

1
1

answered Jul 10 '15 at 18:41

Juan Carlos Oropeza

47,252
12
78
118

I tried something similar to this before, the problem is if you use `order by rand()` which I do because I need random names to be returned than the rowid will not be in the correct sequence – mk_89 Jul 10 '15 at 18:49
What about with this edit, Add the row_id after create the random table? – Juan Carlos Oropeza Jul 10 '15 at 18:53
That's a good idea, I'll give it a try and get back to you with the results – mk_89 Jul 10 '15 at 18:57

Merge two sub-queries row-to-row

1 Answers1