Help with single SQL query
- or -
Post a project like this£250(approx. $314)
- Posted:
- Proposals: 3
- Remote
- #24596
- Expired
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.
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.
Tom W.
0% (0)Projects Completed
-
Freelancers worked with
-
Projects awarded
0%
Last project
4 May 2024
United Kingdom
New Proposal
Login to your account and send a proposal now to get this project.
Log inClarification Board Ask a Question
-
There are no clarification messages.
We collect cookies to enable the proper functioning and security of our website, and to enhance your experience. By clicking on 'Accept All Cookies', you consent to the use of these cookies. You can change your 'Cookies Settings' at any time. For more information, please read ourCookie Policy
Cookie Settings
Accept All Cookies