One row or two?

Question

One row or two?

Terry_Pino

Ten Centuries

Points: 1253
More actions
July 14, 2004 at 9:49 pm

#109093

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Tomkins Ten Centuries Points: 1268 More actions · Answer 1

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

Terry_Pino Ten Centuries Points: 1253 More actions · Answer 2

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

Jonathan Tomkins Ten Centuries Points: 1268 More actions · Answer 3

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

Bob Monahon Ten Centuries Points: 1282 More actions · Answer 4

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