Join two same tables -- sql server programming

Alejandro Mesa
4/3/2006 12:48:01 PM

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

[quoted text, click to view]

Steve Kass
4/3/2006 3:43:37 PM

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

[quoted text, click to view]

Kiran
4/3/2006 8:21:52 PM

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
4/4/2006 12:00:00 AM

[quoted text, click to view]

Kiran
4/4/2006 12:00:00 AM

[quoted text, click to view]

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

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : Join two same tables