Selection Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Selection Problem

Author

Topic

mrsaif
Starting Member

43 Posts

Posted - 2006-06-02 : 06:20:19

I have 2 tables
A
---------------------------------------------------------------
id Name
1 saif
2 Waqar
3 Rajo
4 Kami
5 Mati
6 Vati
7 Coti
8 Foti
9 Kati
10 Qati
______________________________________________________________
another table
B
--------------------------------------------------------------
Bid Cid
1 1
2 2
3 3
4 1
5 1
6 3
7 3
8 4
=========================================
i want to get all records from A whose (id in Bid and cid = 1) and all cid which does not exits in B
I want these results
Id Name
1 saif
4 Kami
5 Mati
9 Kati
10 Qati
=========================How can i do this?????????

Muhammad Saifullah

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-02 : 06:28:56

Select * from A T where not exists(select * from B where bid=T.id)
Union
Select * from A T where exists(select * from B where cid=T.id)

Madhivanan

Failing to plan is Planning to fail

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-02 : 06:38:45

Madhivanan - I think that's not right.

Mrsaif - try this...

--data
declare @A table (id int identity(1, 1), Name varchar(10))
insert @A
          select 'saif'
union all select 'Waqar'
union all select 'Rajo'
union all select 'Kami'
union all select 'Mati'
union all select 'Vati'
union all select 'Coti'
union all select 'Foti'
union all select 'Kati'
union all select 'Qati'

declare @B table (Bid int, Cid int)
insert @B
          select 1, 1
union all select 2, 2
union all select 3, 3
union all select 4, 1
union all select 5, 1
union all select 6, 3
union all select 7, 3
union all select 8, 4

--calculation
Select * from @A A where
    exists (select * from @B where Bid = A.id and Cid = 1) or
    not exists (select * from @B where bid = A.id)

/*results
id          Name       
----------- ---------- 
1           saif
4           Kami
5           Mati
9           Kati
10          Qati
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

mrsaif
Starting Member

43 Posts

Posted - 2006-06-02 : 06:56:05

Thanks Madhivanan and RyanRandall for you kind suggestions But it does not work for me i want to do this in one Query. Now i got the query
which is
select A.* from A left outer join B on a.id = b.id where (b.cid=1 or cid is null)

Muhammad Saifullah

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-02 : 07:06:05

Well, what I gave should have worked for you, but what you have there is better (that's what I get for 'half-copying' Madhivanan

). So, well done

I should have used this in my 'calculation' part...

Select a.* from @A a left outer join @B b on a.id = b.bid where (b.cid = 1 or b.cid is null)

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-02 : 07:31:06

Thanks for the correction

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources