2

Having a bit of trouble with a join statement in some SQL I'm working on. Wondered if there were any smart people out there who would be able to help me with my problem!

I'm trying to JOIN 3 tables: tblMember, tblResult and tblPoint

tblMember - Containing user info such as names and emails. tblResult - Containing details of members and the races they have signed up for, as long as details of their finish positions and times they completed the race in. tblPoint - containing a set of points relating to what position a person finishes in a race, eg 1st place gets 10 points, 2nd gets 8... there are no points assigned if a person finishes 6th or below.

I want the query to return tblResult.ResultID, tblMember.MemberName, tblResult.PersonalTime, tblResult.FinishPosition, tblPoint.Points. BUT I want it to return all of these fields even if FinishPosition is empty and there are no points assigned.

I have almost solved the problem by using this SQL below:

SELECT DISTINCT tblResult.ResultID, tblMember.MemberName, tblResult.PersonalTime, 
tblResult.FinishPosition, tblPoint.Points
FROM (tblMember INNER JOIN tblResult ON tblMember.MemberID = tblResult.MemberID) LEFT   
JOIN tblPoint ON tblResult.FinishPosition = tblPoint.Position;

HOWEVER this doesn't quite work. When my query returns the results, someone who has already been assigned a position, say 2nd place, is returned multiple times with points for 1st place assigned the first time, and then the points for 2nd place assigned the second time!

This has got to be a JOIN issue... But I can't see how to rectify it! I have already tried using IIF and ISNULL but I don't think it was the right thing to use.

I'm using MS Access which appears not to support LEFT OUTER JOIN, RIGHT OUTER JOIN or FULL JOIN.

Can anyone help? Thanks for reading this. :)

EDIT: [tblPoint] contains fields (PointID, RaceTypeID, Points, Position). There are 5 rows for each race type. PointID is the primary key and the table could look like:

Point ID - 1, RaceTypeID - 1, Points - 10, Position - 1

Point ID - 2, RaceTypeID - 1, Points - 6, Position - 2

Point ID - 3, RaceTypeID - 1, Points - 3, Position - 3

Point ID - 4, RaceTypeID - 1, Points - 2, Position - 4

Point ID - 5, RaceTypeID - 1, Points - 1, Position - 5.

This means that for race type 1, whoever comes 1st gets 10 points, 2nd gets 6 points and so on. This table can support different race types, each race type with its own point system.

A member can appear any amount of times in tblResult, once a member has 'signed up' to participate they appear in tblResult, with no finish position or personal time assigned.

LNB
  • 53
  • 1
  • 7
  • 1
    I think the best bet is to post a little sample data. For example, how often can a member appear in the results table? Are there only 5 rows in the points table? – Fionnuala Apr 23 '12 at 15:03
  • Access does support `LEFT OUTER JOIN` and `RIGHT OUTER JOIN` but not `FULL OUTER JOIN` (but note that the union of `LEFT OUTER JOIN` and `RIGHT OUTER JOIN` is the same as `FULL OUTER JOIN`). – onedaywhen Apr 23 '12 at 15:15
  • Just edited my original post to include the information you suggested, is there any chance that makes it clearer? @onedaywhen. Do you reckon it is a FULL OUTER JOIN that I need here? I have seen articles online on how to recreate a FULL OUTER JOIN onlne. – LNB Apr 23 '12 at 15:31
  • 2
    Hi LNB If you have all those extra lines in the points table, you will need to join on race type as well as position. You have not done that in your sample query. – Fionnuala Apr 23 '12 at 19:47

2 Answers2

1

For anyone else that has the same problem:

I was missing another join to the tblRaceType as REMOU pointed out, thank you for that! This was my final SQL code.

SELECT tblResult.ResultID, tblMember.MemberName, tblResult.PersonalTime, 
tblResult.FinishPosition, tblPoint.Points
FROM tblRaceType RIGHT JOIN ((tblMember INNER JOIN tblResult ON tblMember.MemberID = 
tblResult.MemberID) LEFT JOIN tblPoint ON tblResult.FinishPosition = tblPoint.Position) ON 
tblRaceType.RaceTypeID = tblPoint.RaceTypeID;
LNB
  • 53
  • 1
  • 7
-1

MS Access had always problems mixing JOINs and LEFT JOINs. Split the query into two subqueries. This is the best strategy for MS Access, where you don't tweak performance anyway.

And also, if this doesn't help, try to create the queries in graphical interface since MS Access can have problems understanding the SQL source... remember, it's MS :-) Just click. Behave like MS expects - like an easy user :-)

Try these things - i.e. work with Access "as simple as possible" and by my experience you'll find the problem. Also with subquery stragegy (building query using simple steps) you are able to test the sub-results.

Tomas
  • 57,621
  • 49
  • 238
  • 373
  • 6
    "MS Access had always problems mixing JOINs and LEFT JOINs" - that has not been my experience. – Mitch Wheat Apr 23 '12 at 14:18
  • 1
    @MitchWheat totally agree, this can be done without creating two subqueries – Taryn Apr 23 '12 at 14:19
  • At least in queries created in the GUI. – Tomas Apr 23 '12 at 14:28
  • "*This is the best strategy for MS Access, where you don't tweak performance anyway.*" I don't understand that statement. It seems crazy not to tweak the slow parts. – HansUp Apr 23 '12 at 14:31
  • @HansUp, isn't it entirely crazy to use MS Access where performance is important? – Tomas Apr 23 '12 at 14:33
  • 1
    Use Access' db engine when its performance is adequate for the task at hand. I don't think your Access-bashing attitude is really helpful as an answer to the OP's MS Access question. – HansUp Apr 23 '12 at 14:36
  • Definitely agree with the 1st sentence, that's what I'm saying! Not bashing MS Access in any way; it is a good tool for adequate tasks: as a GUI front-end to other database engines or for very small databases that have to be created fast. – Tomas Apr 23 '12 at 14:41
  • 1
    " or for very small databases" Less than 2 gigabytes probably covers a very large number of databases, including community games, for example. – Fionnuala Apr 23 '12 at 14:47
  • 1
    @HansUp: "Use Access' db engine when its performance is adequate for the task at hand" -- I cannot agree your criteria for choosing one DBMS over another. Access has some fairly fundamental flaws and bugs. I don't care how fast it can give me a 'wrong' result. – onedaywhen Apr 23 '12 at 15:22
  • 1
    @HansUp: my last comment applies to database size too: surely you remember [this problem](http://stackoverflow.com/a/9917397/15354) that can be reproduced using a tiny database file? The more salient question are: what is going wrong and what is the product vendor going to do about it? – onedaywhen Apr 23 '12 at 15:24
  • @onedaywhen Performance is definitely not my sole criterion for choosing a DBMS. I didn't anticipate my comment would be interpreted that way. – HansUp Apr 23 '12 at 15:35
  • Have put in more information about the tables I have, does this help at all? Sorry, I just have no idea what to do next! – LNB Apr 23 '12 at 15:45