simple select statement - 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 2005 Forums

Transact-SQL (2005)

simple select statement

Author

Topic

bilencekic
Posting Yak Master

121 Posts

Posted - 2007-12-09 : 08:05:46

hi,
i need a simple select statement
strange it is very simple =)
hourly of working on other things i coulnt think about this.
Well,

aid---oid
1 14
1 15
2 12
2 15

well i need the record that oaid = 14 and oaid = 15.
not 14 or 15 it must be (oaid = 14 and oaid = 15)
i need the result
aid
1

i have some methods but they may slow down server with huge records.

MS BLESS US

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-09 : 08:17:59

will you have more than one record with the same aid and oid ?


select aid
from   table1
where  oid in (14, 15)
group by aid
having count(*) = 2

KH
[spoiler]Time is always against us[/spoiler]

bilencekic
Posting Yak Master

121 Posts

Posted - 2007-12-09 : 08:26:23

yep i have
but in my scenerio i thought that in wont work
yep i think it is fixed , i will test 10-20 min later with a good test conditions.
and no need for count(*)

MS BLESS US

bilencekic
Posting Yak Master

121 Posts

Posted - 2007-12-09 : 08:35:07

well
it didint fixed my problem.
--
my table-

--

46 0
46 73
46 0
46 0
46 76
46 79
46 0
46 81
46 0
47 0
47 75
47 0
47 0
47 76
47 79
47 0
47 81
47 0
48 0
48 73
48 0
48 0
48 76
48 79
48 0
48 81
48 0

left column is aid and right is oid
when u use in for ('73','76')
it gets the aid that have 73 or 76
but i want
the result that have oid = 73 and oid = 76
and result must be

46
48

not 47 (it has 76 but doesnt have 73)

MS BLESS US

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-09 : 08:43:26

[code]select aid
from
(
select aid, oid
from yourtable
where oid in (73, 76)
group by aid, oid
) a
group by aid
having count(*) = 2[/code]

KH
[spoiler]Time is always against us[/spoiler]

bilencekic
Posting Yak Master

121 Posts

Posted - 2007-12-09 : 08:56:34

if u youse in operator you select the wrong result
as you see there
47 has 76 but doesnt have 73
so it shouldnt come
but if you use in operator it looks that if record has 76 OR 73
and 47 comes. that is wrong.
i need just the records which have 76 and 73 in ther oid.

MS BLESS US

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-09 : 09:03:58

[code]DECLARE @sample TABLE
(
aid int,
oid int
)
INSERT INTO @sample
SELECT 46, 0 UNION ALL
SELECT 46, 73 UNION ALL
SELECT 46, 0 UNION ALL
SELECT 46, 0 UNION ALL
SELECT 46, 76 UNION ALL
SELECT 46, 79 UNION ALL
SELECT 46, 0 UNION ALL
SELECT 46, 81 UNION ALL
SELECT 46, 0 UNION ALL
SELECT 47, 0 UNION ALL
SELECT 47, 75 UNION ALL
SELECT 47, 0 UNION ALL
SELECT 47, 0 UNION ALL
SELECT 47, 76 UNION ALL
SELECT 47, 79 UNION ALL
SELECT 47, 0 UNION ALL
SELECT 47, 81 UNION ALL
SELECT 47, 0 UNION ALL
SELECT 48, 0 UNION ALL
SELECT 48, 73 UNION ALL
SELECT 48, 0 UNION ALL
SELECT 48, 0 UNION ALL
SELECT 48, 76 UNION ALL
SELECT 48, 79 UNION ALL
SELECT 48, 0 UNION ALL
SELECT 48, 81 UNION ALL
SELECT 48, 0

SELECT aid
FROM
(
SELECT aid, oid
FROM @sample
WHERE oid IN (73, 76)
GROUP BY aid, oid
) a
GROUP BY aid
HAVING COUNT(*) = 2

/*
aid
-----------
46
48

(2 row(s) affected)
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

bilencekic
Posting Yak Master

121 Posts

Posted - 2007-12-09 : 09:13:10

oh
having count(*) = x
x is the number of values i had sent
i think.
that is great =)
thx man
sorry for letting you the write whole code.

MS BLESS US

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-10 : 02:10:39

or from Tan's example


SELECT	aid
FROM	@sample
GROUP BY aid
HAVING sum(case when 	oid IN (73, 76) then 1 else 0 end)=2

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources