One row or two?
-
Hi All,
Assuming I have the following code, if I have a participant who happens to fit the criteria of having both the JULY and August mail flags set, should I get one or two rows returned.
Select P.PariticipantID from
tblParticipant P
inner join tblDonorMailFlag on
P.PariticipantID = tblDonorMailFlag.ParticipantID
and ((MailPeriodCode = 'AUG' and tblDonorMailFlag.StatusFlag = -1) or
(MailPeriodCode = 'JUL' and tblDonorMailFlag.StatusFlag = -1))
I was always sure that this type of OR clause in a join statement would not result in two or more rows being returned. I was thinking that this could perhaps be a problem with MSSQL 2000, but tried the code on a SQL 7 instance and got the same result?
Regards,
Terry Pino
-
depends what's in you 2 tables
assuming there's only one row in tblParticipant for each participant then you'll only get multiple rows if there's a one to many relationship with tblDonorMailFlag.
From the query it looks like you have multiple rows in there - one for AUG and one for JUL, so you'd get 2 rows back if a participant matches both OR criteria (because 2 rows would match that criteria).
to get back to one, you can just to do a
Select DISTINCT P.PariticipantID from
-
Ouch...not want I wanted to hear! The example I posted was simplified, the statment I was running has a SUM() function in it and consequently the summed figures are doubling up. There is already a Distinct clause on the select statement, but none of the fields in questionable join appear in the select list. All the query is supposed to do is to pick out persons who have one flag or the other selected(or both). I tried a subquery approach (ie select....not in(Flag select statment)) , but due to the size of the flag table this got pretty clunky.
Thanks for the response,
Terry
-
Sounds like the kind of situation where you want to do a subquery early on with the select distinct in it - to basically build an index/limit of the participants you are interested in, then join this back to your original tables to limit the results there and do your summing or whatever on those results.
jt
-
Here are two ideas for you -- neither one uses a NOT IN subquery:
IDEA #1:
Select P.PariticipantID from
tblParticipant P
WHERE P.PariticipantID IN (
SELECT D.ParticipantID FROM tblDonorMailFlag D
WHER ((D.MailPeriodCode = 'AUG' and D.StatusFlag = -1) OR
(D.MailPeriodCode = 'JUL' and D.StatusFlag = -1))
)
IDEA #2:
Select P.PariticipantID from
tblParticipant P
inner join (
SELECT DISTINCT D.ParticipantID
FROM tblDonorMailFlag D
WHERE ((D.MailPeriodCode = 'AUG' and D.StatusFlag = -1)
OR (D.MailPeriodCode = 'JUL' and D.StatusFlag = -1))
) D1
on
P.PariticipantID = D1.ParticipantID
Frankly, for IDEA#2, I've never tried this kind of code, but I've seen it in other posts(!).
Regards,Bob Monahon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply