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
Quote:
Originally Posted by
>From tbl_employee
Where employee_id in (
'009',
'008',
'007',
'007',
'006'
);
alexAlex,
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:
Quote:
Originally Posted by
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
Quote:
Originally Posted by
>>From tbl_employee
Where employee_id in (
'009',
'008',
'007',
'007',
'006'
);
>
alex
>|||>I would like the query to return 5 results; i.e., 4 distinct and one duplicate. <<
The easy way is a UNION, based on a guess about the DDL you did bother
to post and the uniquness of emp_id:
SELECT last_name, first_name
FROM Personnel
WHERE emp_id IN ('009', '008', '007', '006')
UNION
SELECT last_name, first_name
FROM Personnel
WHERE emp_id = '007'|||--CELKO-- (jcelko212@.earthlink.net) writes:
Quote:
Originally Posted by
Quote:
Originally Posted by
Quote:
Originally Posted by
>>I would like the query to return 5 results; i.e., 4 distinct and one
duplicate. <<
Quote:
Originally Posted by
>
The easy way is a UNION, based on a guess about the DDL you did bother
to post and the uniquness of emp_id:
>
SELECT last_name, first_name
FROM Personnel
WHERE emp_id IN ('009', '008', '007', '006')
UNION
SELECT last_name, first_name
FROM Personnel
WHERE emp_id = '007'
Joe, I thought you knew SQL? This query will not return the results
that Alex was asking for.
Why is left as an exercise to the reader.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
