I'm practicing MySQL for an upcoming exam and need some help.
I have this db:
USER(Code, Name, Surname, Age)
THEATRE(Name, City, Capacity)
SUBSCRIPTION(ID, UserCode, TheatreName, Amount)
With these referential integrity constraints:
For exercise I need to write the query which determines code, name and surname of the users older than 50 and who has more than one subscription WITHOUT using the COUNT function.
I know that maybe a self-join could help but I really don't know how. Can anyone help me? Thank you very much.
You can use
SELECT u.Code, u.Name, u.Surname FROM USER u WHERE u.Age > 50 AND EXISTS ( SELECT 1 FROM SUBSCRIPTION s WHERE u.Code = s.UserCode )
SELECT DISTINCT u.Code, u.Name, u.Surname FROM USER u JOIN SUBSCRIPTION s ON u.Code = s.UserCode WHERE u.Age > 50
SELECT DISTINCT u.Code, u.Name, u.Surname FROM USER u JOIN SUBSCRIPTION s1 ON u.Code = s1.UserCode JOIN SUBSCRIPTION s2 ON u.Code = s2.UserCode WHERE s1.ID <> s2.ID AND u.Age > 50