CODEWITHSUNDEEP
sqloracle-databaseoracle11g
zoowix
zoowix

Reputation: 1

SQL query without join

I have the following tables

Table  food                      Table Race                       Table animal
+------------+--------------+    +------------+--------------+     +------------+--------------+
| Quantity   | animal_id    |    | race_code  | race_name    |     |  animal_id | race_code    |
+------------+--------------+    +------------+--------------+     +------------+--------------+

I was asked to calculate the average food quantity for every race (race_name). The challenge here is that I should not use JOIN because we have not studied it yet. I have written the following query:

    select AVG(f.quantity),r.race_name from food f, race r 
    group by r.race_name;

but it doesn't work as I want it to be since it returns the same average food quantity for all races. I know I have to use the animal table to link the other 2 but I didn't know how. I should be using subqueries

Upvotes: 0

Views: 327

Answers (3)

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

select race_name ,(select avg(quantity) from food where animal_id in (select animal_id from animal a where r.race_code = a.race_code))
from race r

Upvotes: 0

derpirscher
derpirscher

Reputation: 17390

You still have to do some matching of related rows. If not explicitly with JOIN you can do it in the WHERE clause. Ie something like

select AVG(f.quantity),r.race_name
from food f, race r, animal a
where f.animal_id = a.animal_id and a.race_code = r.race_code
group by r.race_name;

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142743

That question is exactly the same as your previous, where you had to use SUM (instead of AVG). No difference at all.

Ah, sorry - it wasn't you, but your school colleague, here

Saying that you "didn't learn joins", well - what do you call what you posted here, then? That's a cross join and will produce Cartesian product, once you fix the error you got by not including non-aggregated column into the group by clause and include additional joins required to return desired result.

The "old" syntax is

select r.name,
  avg(f.quantity) avg_quantity
from race r, animal a, food f
where a.race_code = r.race_code
  and f.animal_id = a.animal_id
group by r.name;

What you "didn't learn yet" does the same, but looks differently:

from race r join animal a on a.race_code = r.race_code
            join food f on f.animal_id = a.animal_id

The rest of the query remains the same.

Nowadays, you should use JOINs to join tables, and put conditions into the WHERE clause. For example, condition would be that you want to calculate averages for donkeys only. As you don't have it, you don't need it.

Upvotes: 1

Related Questions