How to convert these 5 queris into 1?

[frostless]

Hi all, I am a newbie of database and I scatch my head so hard but I could not figure this out,:banghead::banghead:

So I have three tables in a database horse, entry, prize and I am supposed to get the result by one query.

The request goes like this :
A list is required of horses that have career winnings better than that of the average horse. List the horse_id, horse name and the career winnings.

A career winning is the sum of all money won by a horse. The solution must include horses that have not won any prizes when calculating average winnings. Remember to exclude the dummy horse entries for Unknown Sire and Unknown Mare.


It is way too complicated for me to get the result by one queries as its nested critera.my solutuin is to create 5 queries with each one getting to the result a bit closer.bit by bit, basing on every previous query, my final query gets the result.



Below are my 5 queries in numeric order:

1:SELECT entry.event_id, entry.horse_id, prize.Money, horse.Horse_name
FROM horse INNER JOIN (prize INNER JOIN entry ON (prize.event_id = entry.event_id) AND (prize.place = entry.place)) ON horse.Horse_id = entry.Horse_id;


2:SELECT horse.Horse_name, [careerwinningssub1].horse_id, Sum([careerwinningssub1].Money) AS careerwinnings
FROM careerwinningssub1 INNER JOIN horse ON [careerwinningssub1].horse_id=horse.Horse_id
GROUP BY horse.Horse_name, [careerwinningssub1].horse_id
ORDER BY [careerwinningssub1].horse_id;

3:SELECT horse.Horse_id, horse.Horse_name, careerwinningssub2.careerwinnings
FROM horse LEFT JOIN careerwinningssub2 ON horse.Horse_id = careerwinningssub2.horse_id
GROUP BY horse.Horse_id, horse.Horse_name, careerwinningssub2.careerwinnings
HAVING (((horse.Horse_name) Not Like "unknow*"))
ORDER BY horse.Horse_id;


4:SELECT (Sum(careerwinnings)/Count(careerwinningsub3.Horse_id)) AS average
FROM careerwinningsub3;


5:SELECT careerwinningsub3.Horse_name, careerwinningsub3.Horse_id, careerwinningsub3.careerwinnings
FROM careerwinningsub3, careerwinningssub4
WHERE (((careerwinningsub3.careerwinnings)>[careerwinningssub4].[average]))
ORDER BY careerwinningsub3.Horse_id;


The tables are


HORSE table
Horse_id Name Colour Sire Dam Born Died Gender
101 Flash white 201 301 2001 0 S
102 Star brown 201 302 2002 0 M
201 Boxer grey 401 501 1991 0 S
301 Daisy white 401 502 1992 0 M
401 Snowy white 9999 9998 1987 1995 S
501 Bluebell grey 9999 9998 1986 1993 M
502 Sally white 9999 9998 1985 1998 M
9998 Unknown dam 9999 9998 0 0 M
9999 Unknown sire 9999 9998 0 0 S

ENTRY table
Event_id Horse_id place
101 101 1
101 102 2
101 201 3
101 301 4
102 201 2
103 201 3
201 101 1
301 301 2
401 102 7

PRIZE table
Event_id Place Money
101 1 120
101 2 60
101 3 30
102 1 10
102 2 5
102 3 2
103 1 100
103 2 60
103 3 40
401 1 1000
401 2 500
401 3 250
401 4 100
401 5 50

Forgive me for making it so complicatedCould someone please shed some light on it? can I combine these 5 queries into just one?Thanks!!!!

 

[plog]

Can you upload your database?

 

[Minty]

So do you need the desired result from one single query?
And how many marks do you get for it?

 

[Ranman256]

UNION query.
The union query will combine many different queries into 1 result.
BUT , EVERY query must have the exact same field count.

 

[frostless]

Can you upload your database?

attached is the database.

my queries are careerwinningsub1 careerwinningssub2 careerwinningssub3 careerwinningssub4 and careerfinal

the desired result is the one from careerfinal but now I need sub1-4 and the final together to get this result. can I combine these into one?

 

[frostless]

So do you need the desired result from one single query?
And how many marks do you get for it?

yeah I think I am required to get the result from just one query, but I made it a bit too clumsy.

 

[frostless]

UNION query.
The union query will combine many different queries into 1 result.
BUT , EVERY query must have the exact same field count.

they don't have the same field count I am afraid

 

[plog]

No offense, but that was a simple Totals Query: https://support.office.com/en-za/ar...-a-query-430a669b-e7fd-4c4b-b154-8c8dbbe41c8a

You were so close with your initial sub query. All I did was apply the correct Total function (GROUP BY, SUM, COUNT, etc.) on each field and remove the event ID field from the SELECT clause:

SELECT horse.Horse_name, horse.Horse_id, Sum(prize.Money) AS CareerWinnings
FROM horse INNER JOIN (prize INNER JOIN entry ON (prize.event_id = entry.event_id) AND (prize.place = entry.place)) ON horse.Horse_id = entry.Horse_id
GROUP BY horse.Horse_name, horse.Horse_id;

My guess is the multiple tables in there threw you. You can make a totals query no matter how many tables your queries requires.

 

[Minty]

@Plog - A+

I think you'll pass...

 

[plog]

And so should he, he was 95% of the way there (got the right answer in the end even).

I have no problem helping people who want to learn with their homework.

 

[Ranman256]

1 either force all queries to have the same field count for the Union (using "" fields)
or
2 write all queries to a 'report' table, then show the table.