Join two same tables

Hi,
I have an employee table. It has fields id, type, ctype1, ctype2.
the data in the table can be like this
1 A 1 1
2 A 1 2
3 A 2 1
4 B 1 1
5 B 2 2
I need a query that would give the below result
1 A 1 1
2 A 1 2
3 A 2 1
5 B 2 2
Get all rows of type A and missing rows from type B
Thanks
KiranKiran,
In SQL Server 2005, you can do this simply
and efficiently as follows:
select id, type, ctype1, ctype2
from (
select
id, type, ctype1, ctype2,
rank() over (partition by ctype1, ctype2 order by type) as rk
from employees
) as T
where rk = 1
In SQL Server 2000, try something like this:
select * from employees E1
where type = 'A'
or not exists (
select * from employees as E2
where E2.type = 'A'
and E2.ctype1 = E1.ctype1
and E2.ctype2 = E1.ctype2
)
-- Steve Kass
-- Drew University
Kiran wrote:

> Hi,
> I have an employee table. It has fields id, type, ctype1, ctype2.
> the data in the table can be like this
> 1 A 1 1
> 2 A 1 2
> 3 A 2 1
> 4 B 1 1
> 5 B 2 2
> I need a query that would give the below result
> 1 A 1 1
> 2 A 1 2
> 3 A 2 1
> 5 B 2 2
> Get all rows of type A and missing rows from type B
>
> Thanks
> Kiran|||Try,
select *
from t1
where c2 = 'a'
union all
select *
from t1 as a
where c2 = 'b' and
not exists(
select *
from t1 as b
where b.c2 = 'a' and b.c3 = a.c3 and b.c4 = a.c4
)
go
AMB
"Kiran" wrote:

> Hi,
> I have an employee table. It has fields id, type, ctype1, ctype2.
> the data in the table can be like this
> 1 A 1 1
> 2 A 1 2
> 3 A 2 1
> 4 B 1 1
> 5 B 2 2
> I need a query that would give the below result
> 1 A 1 1
> 2 A 1 2
> 3 A 2 1
> 5 B 2 2
> Get all rows of type A and missing rows from type B
>
> Thanks
> Kiran
>|||Alejandro Mesa wrote:
> Try,
> select *
> from t1
> where c2 = 'a'
> union all
> select *
> from t1 as a
> where c2 = 'b' and
> not exists(
> select *
> from t1 as b
> where b.c2 = 'a' and b.c3 = a.c3 and b.c4 = a.c4
> )
> go
>
> AMB
>
> "Kiran" wrote:
>
Thanks Alejandro Mesa.|||Steve Kass wrote:
> Kiran,
> In SQL Server 2005, you can do this simply
> and efficiently as follows:
> select id, type, ctype1, ctype2
> from (
> select
> id, type, ctype1, ctype2,
> rank() over (partition by ctype1, ctype2 order by type) as rk
> from employees
> ) as T
> where rk = 1
> In SQL Server 2000, try something like this:
> select * from employees E1
> where type = 'A'
> or not exists (
> select * from employees as E2
> where E2.type = 'A'
> and E2.ctype1 = E1.ctype1
> and E2.ctype2 = E1.ctype2
> )
> -- Steve Kass
> -- Drew University
> Kiran wrote:
>
Hi Steve,
I am using sql 2000.
If I add one more to the table, I have the data as
1 A 1 1
2 A 1 2
3 A 2 1
4 B 1 1
5 B 2 2
6 C 3 1
and using your query return me this
1 A 1 1
2 A 1 2
3 A 2 1
5 B 2 2
7 C 3 1
I need all rows of A and missing rows from B.
so I am using this query as of now
SELECT *
FROM employee
WHERE type = 'a'
UNION ALL
SELECT *
FROM employee AS a
WHERE type = 'b' AND NOT EXISTS
(SELECT *
FROM employee AS b
WHERE b.type = 'a' AND b.ctype1 =
a.ctype1 AND b.ctype2 = a.ctype2)
let me know if there is a better way of doing this and is the above
query ok performance wise.
Thanks
Kiran