CODEWITHSUNDEEP
sqlsql-serverdatabaset-sqldatabase-administration
user20358
user20358

Reputation: 14736

SQL: create a recordset by repeating the output of one query for every record of a second query

I have a query that returns records as below

vw_EmployeeReferenceNumbers

NAME       Number
----       ------
AA         123
AA         234
AA         456

I have another table that returns records like so

AllEmployees

   AllNames
    ----------
    AA
    BB
    CC

I want to output a recordset like so

NAME       Number
----       ------
AA         123
AA         234
AA         456
BB         123
BB         234
BB         456
CC         123
CC         234
CC         456

I dont want to use Cursors at all. I cant modify the view vw_EmployeeReferenceNumbers or the table AllEmployees. Can this be done in SQL?

What I have so far that doesn't work is:

select name, number
from 
(select Name, number, 1 as id from vw_EmployeeReferenceNumbers
) as A
left join
(select name, 1 as id from AllEmployees
) as B
on A.id = B.id

Upvotes: 0

Views: 98

Answers (2)

Vasily
Vasily

Reputation: 5782

SELECT e.name, 
       ern.number
FROM AllEmployees e INNER JOIN
     EmployeeReferenceNumbers ern ON 1=1
ORDER BY e.name

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269653

Use cross join:

select e.name, ern.number
from AllEmployees e cross join
     vw_EmployeeReferenceNumbers ern;

Upvotes: 4

Related Questions