Unanswered: problems getting right data output using sql
What we are doing
I'm currently righting a competition with my brother for the coming english premier ship football season. using an access database as the back end and pushing it to the web via asp.
I'm having a problem with my sql I needed to get a list of all people that belong to a league plus there score. (sounds simple hang on moment and please read on)
The players are contain within a table called tblleaguemembers and the points are store in a table called tblpointsweekly. The link between these fields is identrant. Each user can have a maxmium of ten entries per week and the best score counts.
Players can join the competiton and not yet have a record in tblpointsweekly. The score for that week is reduced to the highest score each player has by using the max aggregate function. and it is also filter to show only people from a certain league.
I have been able to get a list of all players that have an entry in tblleaguemembers and an entry in tblpointsweekly. This does not however show the people who do not yet have an entry in tblpointsweekly.
Is there any way to do either
1) Get a list that contains all the entrants that are tblleaguemembers and there score where appropriate.
2)A list of all entrants that don't appear in tblpointsweekly but do in tblleaguemembers.
Either answer will do as on the second one can added to the bottom of the tempary table I have in memeory created using asp code.
There are a number of other tables that you will see in the code bellow. these are tblweeks this stores the date. tblentrants this stores entrant details and tbleagues this stores league information.
Currently work sql
SELECT tblLeagues.idLeagues, tblLeagueMembers.idEntrant, tblPointsWeekly.idEntrant, tblEntrants.FullName, tblLeagueMembers.MemberConfirmed, tblWeeks.Date, Max(tblPointsWeekly.Score) AS MaxOfScore
FROM tblWeeks INNER JOIN (tblLeagues INNER JOIN ((tblEntrants INNER JOIN tblLeagueMembers ON tblEntrants.idEntrant = tblLeagueMembers.idEntrant) INNER JOIN tblPointsWeekly ON tblEntrants.idEntrant = tblPointsWeekly.idEntrant) ON tblLeagues.idLeagues = tblLeagueMembers.idLeague) ON tblWeeks.idWeek = tblPointsWeekly.idWeek
GROUP BY tblLeagues.idLeagues, tblLeagueMembers.idEntrant, tblPointsWeekly.idEntrant, tblEntrants.FullName, tblLeagueMembers.MemberConfirmed, tblWeeks.Date
ORDER BY tblPointsWeekly.idEntrant, Max(tblPointsWeekly.Score) DESC;
PS I do not wish to have queries in the datebase or to create other tables in access.
IF ANY ONE CAN HELP PLEASE. If you need any further explanation then please don't hesitate to ask.
I normally set up my SQL statements as queries and then use the SQL generated. I think you need a right join between tblLeagueMembers and tblPoints weekly. The type of join that shows all from members and only matching records from points.
I have looked in to that pry to posting but all I seem to get is access telling me that I have an ambigouse join and it will not run. It also tells me to use a query to get some of the data first and then use this query with the one I'am creating. well I have tried every combination I can think of with mixed results sadly not the right one.