Help with single SQL query

  • Posted
  • Proposals 3
  • Remote
  • #24596
  • Expired
Ben P.N M.Genuine W. have already sent a proposal.
  • 1

Description

Experience Level: Expert
Trying to convert Access query into SQL for MySQL-PHP webpage.

Query uses two tables, if_shows and if_showstats.
if_shows are exhibitions with a unique field ShowID as primary key.
if_showstats are statistics relating to the show for a specific year under a specific heading (e.g. gross area, number of visitors)

Each record in if_showstats has the following significant fields:

ShowRef: foreign key corresponding to if_shows.ShowID

HeadingRef: integer, values 1-9, e.g. gross area = 1, number of visitors = 9

CalYear: year in which show took place (most shows annual)

Result: integer, value required

The aim is to collect all shows\' latest results for a given HeadingRef.

Query 1: find latest set of figures per show

Max Dates = SELECT if_ShowStats.ShowRef, Max(if_ShowStats.CalYear) AS MaxOfCalYear
FROM if_ShowStats
GROUP BY if_ShowStats.ShowRef;

Query 2: find results for all shows for given HeadingRef

SELECT if_shows.ShowShortName, if_ShowStats.CalYear, if_ShowStats.ShowRef, if_ShowStats.HeadingRef, if_ShowStats.Result
FROM (if_ShowStats INNER JOIN MaxDates ON if_ShowStats.CalYear = MaxDates.MaxOfCalYear) LEFT JOIN if_shows ON if_ShowStats.ShowRef = if_shows.ShowID
WHERE (((if_ShowStats.HeadingRef)=1));


REQUIRED: Query 3: incorporate Query 1 into Query 2 as standalone SELECT query.

Clarification Board

    There are no clarification messages.