473,238 Members | 1,800 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,238 software developers and data experts.

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','I ndividual','Team'),p.displayname,se.subeventID,st. subregtypename,ct.categoryname

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 |
------------------------------------------------------------------------

Feb 14 '06 #1
2 1264
"Pasquale" <sp*****@NOTHNXtelusplanet.net> 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','I ndividual','Team'),p.displayname,se.subeventID,st. subregtypename,ct.categoryname
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

Feb 14 '06 #2
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.


Feb 20 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: James | last post by:
Hi, I am making a basic DVD shop and after having produced code which displays a list of DVDs from my database, I implemented a search facility which enables the user to search by title, director...
9
by: Chris Greening | last post by:
I'm seeing a very strange problem with outer joins. The example below replicates the problem: create table data1 (dim1 integer, stat1 float); create table data2 (dim1 integer, stat2 float); ...
13
by: NM | last post by:
Sometimes ago I was having a problem in linking between C++ and Fortran program. That was solved (using input from this newsgroup) using the Fortran keyword "sequence" with the derived types (to...
0
by: Ahmed | last post by:
Hi, I'm an Access newbie and I'm trying to create a simple report binded to a query which is: SELECT survey.q0_sex AS Gender, Count(survey.q0_sex) AS FROM survey WHERE q0_sex<>"" GROUP BY...
0
by: Tim | last post by:
I am currently running a search via Index Server and allowing users to Search within Results. To search within results, I loop through the original dataset and get the WorkIDs. I then run a new...
1
by: mirela | last post by:
Hello, I'm using RedHat 9 and PostgreSQL 7.3.4. I have a table with a column of type varchar and length 250. This column includes both English and Hebrew text values. The following select...
1
by: Sandesh | last post by:
Hello All, Me saying " has any body come across such error would be underestimating". Well I am getting a very peculiar and unique error "Line 1: Incorrect syntax near 'Actions'." ...
1
by: bgreenspan | last post by:
Hi Everyone, I'm back for some more expert help. Here's what I am doing and what I tried. My database has entries with Contract Names and Expiry Dates, among other fields. I have a form...
7
by: atkellyx | last post by:
Hi I have often found over the year odd maths stuff that queries can return (eg 1*5 = 4.9999999999999999). I have always got around them but have a problem now which has me stumped. To...
1
by: stockton | last post by:
I have written the following Stored Procedure and need input as to what could be wrong with my coding or is there a possibility that not getting the correct results is not in the procedure at all? ...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.