Posted by Steve Kass on 02/27/07 15:36

Alex,

There are a few solutions. Two are (might have typos, but you should be
able to get the idea):

select Employee_last_name, Employee_first_name
from tbl_employee
join (
select '009' as id union all
select '008' as id union all
select '007' as id union all
select '007' as id union all
select '006' as id
) as IDs
on IDs.id = tbl_employee.employee_id

or to make the specification of ids simpler:

declare @ids varchar(1000)
set @ids = '009008007007006'
declare @idlength int
set @idlength = 3

select Employee_last_name, Employee_first_name
from tbl_employee
join a_permanent_table_of_integers_from_0_to_whatever as Nums
on employee_id = substring(@ids,@idlength*n+1,@idlength)
and n < len(@ids)/@idlength
-- [n] is the column name for the permanent table and should
-- be that tables primary key

-- Steve Kass
-- Drew University
-- http://www.stevekass.com


alex wrote:
> Hello experts,
>
> I'm trying the run the following query with specific intentions.
>
> I would like the query to return 5 results; i.e., 4 distinct and one
> duplicate. I am only getting, however, 4 distinct records. I would
> like the results from the '007' id to spit out twice.
>
> I'm not using 'distinct,' and I've tried 'all.' I realize that I
> could put my 5 employee id's in a table and do a left or right join; I
> would like to avoid that, however. Any thoughts?
>
> Select
> Employee_last_name,
> Employee_first_name
>>From tbl_employee
> Where employee_id in (
> '009',
> '008',
> '007',
> '007',
> '006'
> );
>
> alex
>

[Back to original message]