SQL Querry - 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)

SQL Querry

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-03-07 : 07:44:14

Kunaal writes "I have a master table ministry_master with column ministry_id and a ministry_locale table that has cols ministry_id(foreign key relationship with Ministry_master.Ministry_id) and culture_id. Now for every Ministry_id, value, the locale will have culture_id value 1 and 2 such that a typical table will look like

Ministry_id Culture_Id

1 1
1 2
2 1
2 2
3 1
3 3
4 2
5 2
6 2

I need to pick rows with culture_id = 2 for which, culture_id = 1 Row doesnt exist. Could you tell me the sql for this?

In this example, the result set would be

4
5
6

thanks and regards"

mr_mist
Grunnio

1870 Posts

Posted - 2005-03-07 : 08:34:53

SELECT ministry_id from ministry_locale minl1
LEFT OUTER JOIN ministry_locale minl2 on minl1.Ministry_id = minl2.Ministry_id and minl2.culture_id = 1
WHERE minl1.culture_id = 2 and minl2.ministry_id is null

EDIT : No that wont work, sorry.

-------
Moo. :)

Chester
Starting Member

27 Posts

Posted - 2005-03-07 : 08:43:56

select Ministry_id
FROM ministry_master
where Ministry_id not in (select Ministry_id from test where Culture_Id = '1')
and Culture_Id = '2'

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-07 : 09:04:30

mr mist, why won't that work? This looks like the same thing and it works. Anyway Kunaal, looks like you've got some options.


Select	distinct a.ministry_id
From	@ministry_locale a
Left JOIN @ministry_locale b
		ON a.ministry_id = b.ministry_id
		and b.culture_id = 1
where	a.culture_id = 2
AND	b.culture_id is NULL

Be One with the Optimizer
TG

mr_mist
Grunnio

1870 Posts

Posted - 2005-03-07 : 09:44:32

Yeah I was missing the distinct.

-------
Moo. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources