And / And question to slow :-(

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

Other Forums

MS Access

And / And question to slow :-(

Author

Topic

neutcomp
Posting Yak Master

111 Posts

Posted - 2008-04-04 : 07:53:00

Hello I have a database:

ID patnummer zorgtype
1 1029 A
2 1029 B
3 1029 C
4 1030 C
5 1030 A
6 1040 B
7 1050 A
8 1050 B
9 1060 B
10 1060 C

The Query that gives me the correct result is:

SELECT p1.patnummer, p1.zorgtype
FROM Patienten AS p1, Patienten AS p2
WHERE
(
(
p1.zorgtype = "A"
AND p2.zorgtype = "B"
)
OR (
p1.zorgtype = "B"
AND p2.zorgtype = "A"
)
)
AND p1.patnummer = p2.patnummer

The Result:

patnummer zorgtype
1029 B
1029 A
1050 B
1050 A

So I want alle persons back if the have the zorgtype A and B.

In a small database this works fine but when i use this query in production its to slow.

Can somebody maby make a faster query with the same result?

Thanks
Bjorn

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-04-04 : 10:43:36

select patnummer, count(*) from mytable
where zorgtype in ('A','B')
group by patnummber
having count(*) = 2

neutcomp
Posting Yak Master

111 Posts

Posted - 2008-04-04 : 10:51:40

quote:
Originally posted by AndrewMurphy

select patnummer, count(*) from mytable
where zorgtype in ('A','B')
group by patnummber
having count(*) = 2

Ok but then I get back:

patnummer count(*)
1029 2
1050 2

And not
patnummer zorgtype
1029 B
1029 A
1050 B
1050 A

So the query works but its not the one to get the right information.
Thank for the help.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-04-04 : 11:40:59

"So I want alle persons "...I've given you the code for the requirement
but what you SEEM to want is
"So I want alle persons AND the cases they are involved with"..which is a slightly different requirement

...but since all persons are invovled with cases A+B...what's the point in putting them in the result set...it doesnt' add much.

I'm also not sure what you want to do with patient "1029 + C"...do you want this entry in the resultset?

Anyway....what I've given you can be used to drive your end result....just join my resultset back to the main data set...it's just the next step in the development of the code....search here for other examples (expecially from tkizer, nr). (me doing it for you won't advance your knowledge).

Also make sure you have an index on patnummer to help with the performance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources