At the moment I have it find the oldest clerks living in a city, but somehow I have to change it to oldest clerks in the stores of a city. I have tried adding the StoreID in there, but it only messes things up. I also added a picture of the relationships

SELECT Name AS [Name of the oldest clerk]
FROM Clerks AS c, Cities AS ct, Stores AS s
WHERE ct.CityID=c.CityID
AND s.StoreID=c.StoreID
AND City=[Name of the city]
AND Age=(SELECT MAX(Age)
               FROM Clerks AS c, Cities AS ct
               WHERE ct.CityID=c.City
               AND City = [Name of the city]);

CodePudding user response：

Not tested yet, just an idea. I'll take a better look in a moment.

select cy.city,c.name,c.age,max_age.mx_age
from
clerks as c
join stores as s on c.storeid=s.storeid
join cities as cy on s.cityid=cy.cityid
join 
(
    select s2.cityid,max(c2.age) as mx_age from stores as s2
    left join clerks as c2 on s2.storeid=c2.storeid
    group by s2.cityid
) as max_age_by_city on s.cityid=max_age_by_city.cityid and c.age=max_age_by_city.mx_age
order by cy.city,c.age,c.name

CodePudding user response：

Select c.* 
from Clerks c
    join Stores s on c.StoreID = s.StoreID
    join Cities ct on ct.CityID= s.CityID
    join ( select max(C2.Age) as Age, S2.CityID 
            from Clerks as C2 
            join Stores as S2 on C2.StoreID = S2.StoreID        
            group by S2.CityID) as MaxAge 
        on c.Age = MaxAge.Age 
        and s.CityID = MaxAge.CityID
where ct.City="London"

I've joined to Clerks the tuple of (max(Clerks.Age), Stores.City)

Fiddle Here

EDIT: joined Strores table to find max Age by store's city

CodePudding user response：

Without all those joins :

SELECT ... FROM CLERKS C
 WHERE NOT EXISTS (SELECT 1 FROM CLERKS C2
                    WHERE C2.CITYID = C.CITYID AND C2.AGE > C.AGE)

SELECT ... FROM CLERKS C
 WHERE NOT EXISTS (SELECT 1 FROM CLERKS C2
                    WHERE C2.STOREID = C.STOREID AND C2.AGE > C.AGE)

As for "oldest clerks in the stores of a city" it should then become

SELECT ... FROM CLERKS C JOIN STORES S ON ...
 WHERE NOT EXISTS (SELECT 1 FROM CLERKS C2
                    WHERE C2.STOREID = C.STOREID AND C2.AGE > C.AGE)
   AND S.CITYID = ...