user3515916 user3515916 - 5 months ago 25
SQL Question

MySQL counting elements without using count(*)

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:

SUBSCRIPTION.UserCode->USER.Code

SUBSCRIPTION.TheatreName->THEATRE.Name

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.

Answer

You can use

EXISTS:

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
)

Or JOIN

SELECT DISTINCT u.Code, u.Name, u.Surname
FROM USER u
JOIN SUBSCRIPTION s
ON u.Code = s.UserCode
WHERE u.Age > 50

Edited:

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