CODEWITHSUNDEEP
sqloracle-databasejoin
Gentian Gashi
Gentian Gashi

Reputation: 361

Tables Won't Join Properly

I'm trying to list all the users who are above the age of 13 and have downloaded an app in the category "Social". With the users' date of births in ascending order.

I'm failing to understand what is wrong with my join and how I should be joining these tables together in SQL.

Code below: 

SELECT DISTINCT
u."UserId",
CAST(u."Username" AS varchar2(20)) AS "Username",
CAST(u."FirstName" AS varchar2(15)) AS "FirstName",
CAST(u."LastName" AS varchar2(15)) AS "LastName",
CAST(u."Email" AS varchar2(30)) AS "Email",
u."Gender",
u."JoinDate",
TRUNC(u."DateOfBirth") AS "DateOfBirth",
u."CountryId"
FROM BR_USER u JOIN BR_APPUSER ap ON u."UserId" = ap."UserId", BR_APPUSER ap JOIN BR_APPCATEGORY ac ON ap."AppId" = ac."AppId"
WHERE months_between(TRUNC(sysdate), u."DateOfBirth") / 12 > 13 AND ac."CategoryName" = 'Social'
ORDER BY "DateOfBirth" ASC;

My Join : 

FROM 
BR_USER u JOIN BR_APPUSER ap ON u."UserId" = ap."UserId", 
BR_APPUSER ap JOIN BR_APPCATEGORY ac ON ap."AppId" = ac."AppId"

I am trying to list only the users who have the CategoryName = "Social" enter image description here

Upvotes: 0

Views: 45

Answers (1)

Abra
Abra

Reputation: 20914

According to the Entity-Relationship (ER) diagram you posted, you need to join four tables, namely User, AppUser, App and AppCategory.

select U.username
from
  User U
inner join
  AppUser AU
on
  U.userid = AU.userid
inner join
  APP A
on
  AU.appid = A.appid
inner join
  AppCategory AC
on
  AC.appCategoryId = A.appCategoryId
where
  AC.categoryName = 'Social'

Upvotes: 1

Related Questions