incorrect SELECT results

I am using the query below for a multi event registration confirmation
page. Any one of these events can have individuals and/or teams, which
also then can have different categories of individuals or teams.

I came upon a glitch in that if a captain has more than one team
registration for different events the team names for all his/her events
show up on the confirmation page for each event. So...

Captain1:
Team1 - Event1
Team2 - Event2

Confirmation page Event1 shows
Team1
Team2

instead of just Team1

Thanks.


QUERY

SELECT IF(se.regtype = 'i',CONCAT(p.lname,',
',p.fname),t.teamname),p.city,IF(se.regtype='i','Individual' ,'Team'),p.displayname,se.subeventID,st.subregtypename,ct.ca tegoryname

FROM
(((((((participant2006_copy as p
LEFT JOIN captain2006_copy as c ON p.participantID=c.relparticipantID)
LEFT JOIN team2006_copy as t ON t.relcaptainID=c.captainID)
INNER JOIN registration2006_copy as r ON
p.participantID=r.relparticipantID)
INNER JOIN regdsubevt2006_copy as rs ON
rs.relregistrationID=r.registrationID)
LEFT JOIN subevent2006_copy as se ON rs.relsubeventID=se.subeventID)
LEFT JOIN category2006_copy as ct ON se.subeventID=ct.relsubeventID)
INNER JOIN subregtype as st ON se.relsubregtypeID=st.subregtypeID)
WHERE rs.relsubeventID IN ($subevtimplode)
ORDER BY rs.relsubeventID,t.teamname,p.lname,p.fname ASC


EXPLAIN

------------------------------------------------------------ ------------
|table|type |possible| key |key_len| ref |rows| Extra
_keys
------------------------------------------------------------ ------------
| rs |ALL | 55 | Using
where;
temporary;
filesort
------------------------------------------------------------ ------------
| r |eq_ref |PRIMARY |PRIMARY| 2 |rs.rel
registrationID| 1 |
------------------------------------------------------------ ------------
| p |eq_ref |PRIMARY |PRIMARY| 8 |r.rel
participantID | 1 | Using
where;
------------------------------------------------------------ ------------
| se |eq_ref |PRIMARY |PRIMARY| 2 |rs.rel
subeventID | 1 |
------------------------------------------------------------ ------------
| ct |ALL | | 7 |
------------------------------------------------------------ ------------
| c |ALL | | 8 |
------------------------------------------------------------ ------------
| t |ALL | | 8 |
------------------------------------------------------------ ------------
| se |eq_ref |PRIMARY |PRIMARY| 2 |se.rel
subregtypeID | 1 |
------------------------------------------------------------ ------------

Re: incorrect SELECT results

"Pasquale" wrote in message
news:k7cIf.140$_62.80@edtnps90...
> I came upon a glitch in that if a captain has more than one team
> registration for different events the team names for all his/her events
> show up on the confirmation page for each event. So...
>
> Captain1:
> Team1 - Event1
> Team2 - Event2
>
> Confirmation page Event1 shows
> Team1
> Team2
>
> instead of just Team1

I think the query is doing exactly what it is supposed to, given the
structure of your joins.
Perhaps by diagramming the joins it will be more clear. I'm trying to mimic
a one-to-many entity relationship by using "--<".

[t] >-- [c] >-- [p] --< [r] --< [rs] --< [se] --< [ct]

and also [se] --< [st]

You have a WHERE clause that is restricting the subevent ID's in [rs].
But then through these joins, you link to the registered participant, who
may be a captain, who may be associated with multiple teams. All those
one-to-many relationships add up!

You need to restrict the query somehow so that you get only the team that is
associated _both_ with the captain [c] and the subevent identified in [rs].
This could be done in your WHERE clause, for instance.

How are teams associated with events in your schema? Is there a field in
[rs] for it?

In other words, if I were to ask you for the list of teams who attended a
given event (regardless of individual participants -- just interested in the
teams), could you make a query to give me the answer?

I'm looking back at the schema you posted on 2/6/2006. I don't see any
obvious solution given the tables and fields. In other words, given your
current schema, this problem seems to be unanswerable -- you aren't
recording all the data you need.

You may have to introduce another table, this one a many-to-many table, that
satisfies the following relationship:

[t] >-- [ts] --< [se]

The table is as follows:

create table teamsubevt2006 (
`relteamID` smallint(3) unsigned not null,
`relsubeventID` smallint(3) unsigned not null,
primary key(`relteamID`, `relsubeventID`)
)

Populate this table when captains register for events. If a captain has
only one team, there is only one possibility. If the captain has more than
one team, you have to prompt him/her for which team he/she is registering.

Your query needs another join in it:

LEFT OUTER JOIN teamsubevt2006 as ts ON ts.relteamID = t.teamID AND
ts.relsubeventID = rs.relsubeventID

As a side note, since [se] --< [st] is an INNER JOIN, then [rs] --< [se]
might as well be too. Your query might speed up a bit.

Regards,
Bill K.

> QUERY
>
> SELECT IF(se.regtype = 'i',CONCAT(p.lname,',
> ',p.fname),t.teamname),p.city,IF(se.regtype='i','Individual' ,'Team'),p.displayname,se.subeventID,st.subregtypename,ct.ca tegoryname
> FROM
> (((((((participant2006_copy as p
> LEFT JOIN captain2006_copy as c ON p.participantID=c.relparticipantID)
> LEFT JOIN team2006_copy as t ON t.relcaptainID=c.captainID)
> INNER JOIN registration2006_copy as r ON
> p.participantID=r.relparticipantID)
> INNER JOIN regdsubevt2006_copy as rs ON
> rs.relregistrationID=r.registrationID)
> LEFT JOIN subevent2006_copy as se ON rs.relsubeventID=se.subeventID)
> LEFT JOIN category2006_copy as ct ON se.subeventID=ct.relsubeventID)
> INNER JOIN subregtype as st ON se.relsubregtypeID=st.subregtypeID)
> WHERE rs.relsubeventID IN ($subevtimplode)
> ORDER BY rs.relsubeventID,t.teamname,p.lname,p.fname ASC
>

Re: incorrect SELECT results

Thanks alot Bill!! I will take a closer look at this and trying all your
suggestions in a couple of weeks. I've answered your questions below.

Thanks again.

Bill Karwin wrote:
>
> How are teams associated with events in your schema? Is there a field in
> [rs] for it?
>
> In other words, if I were to ask you for the list of teams who attended a
> given event (regardless of individual participants -- just interested in the
> teams), could you make a query to give me the answer?

I didn't create a direct team (team2006) to registered subevent
(regdsubevt2006) relationship. I thought it would be redundant to do so
since I had set up a distant relationship in the following way...

team2006.relcaptainID -> captain2006.captainID
captain2006.relparticipantID -> participant2006.participantID
participant2006.participantID -> registration2006.relparticipantID
registration2006.registrationID -> regdsubevt2006.relregistrationID

....regdsubevt2006 has the relsubeventID column for which they are
registered for.

In my attempts to avoid redundancy, I missed simplicity as well as your
scenario question.

>
> I'm looking back at the schema you posted on 2/6/2006. I don't see any
> obvious solution given the tables and fields. In other words, given your
> current schema, this problem seems to be unanswerable -- you aren't
> recording all the data you need.
>
> You may have to introduce another table, this one a many-to-many table, that
> satisfies the following relationship:
>
> [t] >-- [ts] --< [se]
>
> The table is as follows:
>
> create table teamsubevt2006 (
> `relteamID` smallint(3) unsigned not null,
> `relsubeventID` smallint(3) unsigned not null,
> primary key(`relteamID`, `relsubeventID`)
> )
>
> Populate this table when captains register for events. If a captain has
> only one team, there is only one possibility. If the captain has more than
> one team, you have to prompt him/her for which team he/she is registering.
>
> Your query needs another join in it:
>
> LEFT OUTER JOIN teamsubevt2006 as ts ON ts.relteamID = t.teamID AND
> ts.relsubeventID = rs.relsubeventID
>
> As a side note, since [se] --< [st] is an INNER JOIN, then [rs] --< [se]
> might as well be too. Your query might speed up a bit.
>
> Regards,
> Bill K.
>