September 20, 2007 at 1:19 pm
trying to do a count on two tables on joins but it's as if both count gives me the same value.
can u guys help me out with this one. thx
select
p.KPAIDS_NO, count(D.Date_Delivered) as 'Total Current delivery'
, count(po.Year_Of_Delivery)
from
Patient_Demographics p
left
outer join
Patient_Pregnancy_Outcome D
on
p
.KPAIDS_NO = D.KPAIDS_NO
inner
join
Patient_Obstetrical_History po
on
D
.KPAIDS_NO = po.KPAIDS_NO
where
p.Mother_or_Child = '2'
group
by p.KPAIDS_NO
having
count
(D.Date_Delivered) >1
or
count
(po.Year_Of_Delivery)>1
September 20, 2007 at 1:34 pm
Still need more information to understand what you want to accomplish and that is what does each table and value used represent (some look easy to figure out but I don't want to assume anything)
But as for why you get the same count consider this, if you take everything else away but the relationship ot D and po (where your count of dates come from) you get
dbo.Patient_Pregnancy_Outcome D
INNER JOIN
dbo.Patient_Obstetrical_History po
ON
D.KPAIDS_NO = po.KPAIDS_NO
which means every record has to match at least one or more records and thus the counts will be the same. It also looks like if you have more than one pregnancy with an out come you build in multiples of that the count.
1 preg = 1 date
2 pregs = 4 dates
3 pregs = 9 dates
and so one because nothing makes them distinct. As well, your logic still seems very vague to me. What is your goal? What does your data look like and how does it all fit together?
September 20, 2007 at 1:41 pm
ok, well there are two tables one with pregnacy hist and the other with current pregnacy.
What i want to do is the count the total number of pregnacy greater than 1 in both tables and display it in a single resultset.
September 20, 2007 at 1:47 pm
OK so let me clarify, does the history table include the current pregnancy and does the other include any info on past pregnancies?
September 20, 2007 at 1:56 pm
Denby - you need to create the counts in separate sub-queries FIRST, then join them. If you join them first - your counts will be wrong (most likely - for both aggregates.)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 20, 2007 at 1:57 pm
>>pregnancy history table contain pregnancies (or delivery dates) that might not be in Current pregnancy table. The history table has delivery dates as Years only while the current table has full (day,month,year) dates.
so i want count those with multiple "Delivery dates" from each table.
September 20, 2007 at 2:04 pm
As in:
select p.KPAIDS_NO, [TotalCurrentDelivery]
, Year_Of_DeliveryCount
from
Patient_Demographics p
left outer join
(select kpaid_no, count(D.Date_Delivered) as [TotalCurrentDelivery]
from
Patient_Pregnancy_Outcome
group by kpaid_no)
D
on
p
.KPAIDS_NO = D.KPAIDS_NO
inner join
(select kpaid_no, count(po.Year_Of_Delivery) as [Year_Of_DeliveryCount]
from
Patient_Obstetrical_History
group by kpaid_no)
p0
on
D
.KPAIDS_NO = po.KPAIDS_NO
where
p.Mother_or_Child = '2'
and
(TotalCurrentDelivery
>1
or
Year_Of_DeliveryCount
>1)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 20, 2007 at 2:36 pm
Thx allot Matt
much respect
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply