IS there a better way to write this query
-
Hi,
Is there a better way to write following query ?
select i.csuflxideb,i.csddedcode,i.csddeds2code
from
(select csuflxideb,csddedcode,csddeds2code
from checksumm,checksummded
where csuflxid=csdflxidcsu
and csupayperiod like '2005%'
group by csuflxideb,csddedcode,csddeds2code)i
group by csuflxideb,csddedcode,csddeds2code
having count(*)>1
order by 1,2,3
Kavita
-
Try this
select csuflxideb,csddedcode,csddeds2code
from checksumm
inner join checksummded
on csuflxid=csdflxidcsu and csupayperiod like '2005%'
group by csuflxideb,csddedcode,csddeds2code
having count(*) > 1
order by 1, 2, 3
-
Yes, there is a better way as Jeff writes.
select csuflxideb,
csddedcode,
csddeds2code
from checksumm
inner join checksummded on csuflxid = csdflxidcsu
where csupayperiod like '2005%'
group by csuflxideb,
csddedcode,
csddeds2code
having count(*) > 1
order by csuflxideb,
csddedcode,
csddeds2code
Also, it is good practice to prefix the columns for easier reading when dealing with more than 1 table in a query.
Example:
select checksumm.csuflxideb,
checksumm.csddedcode,
checksummded.csddeds2code
from checksumm
inner join checksummded on checksummded.csuflxid = checksumm.csdflxidcsu
where checksummded.csupayperiod like '2005%'
group by checksummd.csuflxideb,
checksummd.csddedcode,
checksummded.csddeds2code
having count(*) > 1
order by 1,
2,
3
N 56°04'39.16"
E 12°55'05.25" -
actualy JeffB and Peter Larsson altered your query simanticaly
This way they corrected your query
The way you have written it, it should not return any rows at all !
because your having clause is only applied on the group by of the resultset of your nested table expression (i). This already contains a group by on all the columns that are returned, so will only return 1 row per column-combination.select i.csuflxideb,i.csddedcode,i.csddeds2code
from
(select csuflxideb,csddedcode,csddeds2code
from checksumm,checksummded
where csuflxid=csdflxidcsu
and csupayperiod like '2005%'
group by csuflxideb,csddedcode,csddeds2code)i
group by csuflxideb,csddedcode,csddeds2code
having count(*)>1
order by 1,2,3
BTW Peter ... thumbs up for the very good programming guidlines !
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Thanks.
N 56°04'39.16"
E 12°55'05.25"
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply