selectuing unique data from a table

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

SQL Server Development (2000)

selectuing unique data from a table

Author

Topic

patman
Starting Member

6 Posts

Posted - 2006-07-28 : 06:14:38

hi all, pretty new to sql - have a query i'm trying to do but not succeeding -

I have a table as follows - it has 2 columns:

deal job
1 x
2 x
4 x
1 y
3 y
4 y

what I want to do is write 2 queries to find all the deals that are in job x, but not in job y, and all the deals that are in y but not in job x

In this case, jobs in x, but not in y is job 2, and jobs in y but not in x is job 3.

I tried to do a select where job = x and job !=y, but that is not correct. any ideas please?

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-07-28 : 06:20:35

try this...

i)
what I want to do is write 2 queries to find all the deals that are in job x, but not in job y, and all the deals that are in y but not in job x
------>
1st qry -> Select Deal From <Ur Table> Where Job In ('X')

2nd qry -> Select Deal From <Ur Table> Where Job In ('Y')

hopes u r looking for this one. but still u need another o/p just mentioned the desired o/p.

Mahesh

patman
Starting Member

6 Posts

Posted - 2006-07-28 : 06:24:36

thanks mahesh - i tried this, but the problem is that it doesnt give me the ones that are unique to each Job. I am not sure how I get the unque ones.

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-07-28 : 06:26:20

thats what i m asking for. just mention the o/p. we will try at or best

Mahesh

Kristen
Test

22859 Posts

Posted - 2006-07-28 : 06:28:47

To get all JOBs that only have one DEAL:

SELECT MIN(deal)
FROM MyTable
GROUP BY job
HAVING COUNT(distinct DEAL) = 1

(that's not restricted to jobs X and Y, so will show you all deals that fit this criteria - which may NOT be what you want!)

Kristen

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-29 : 09:42:31

[code]
declare @table table
(
deal int,
job char(1)
)
insert into @table
select 1, 'x' union all
select 2, 'x' union all
select 4, 'x' union all
select 1, 'y' union all
select 3, 'y' union all
select 4, 'y'

select deal as [deal in x but not y]
from @table
where job = 'x'
and deal not in (select deal from @table where job = 'y')

deal in x but not y
-------------------
2

select deal as [deal in y but not x]
from @table
where job = 'y'
and deal not in (select deal from @table where job = 'x')

deal in y but not x
-------------------
3
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources