SQL join query

catching_streams

Hi,

I've got a query i'm having trouble with and would appreciate some feedback! Apologies for the table layout

I'm trying write a query to extract all unique parent accounts, along with the department and company information based on the below 4 tables:

The parent accounts have sub accounts and these subaccounts contain the department id which is linked to department table

The parent accounts department will always be the subaccounts department and these will always be the same.

Department
DepartmentId

DepartmentName
1000 BILLING
2000 HR

SubAccount
SubAccId ParentAccId SubAccName SubAccDeptId
1 20 Expenses 1000
2 20 Overhead 1000
3 30 Finance 1250

ParentAcc
PiD ParentAccName CompanyId
75 ProductionLine 50
76 Shipping 89

Company
Cid CompanyName
50 PinkLadyApplesCo
89 PellegrinoWaterCo


My query is unfortunately listing all subaccounts per parent account along with the company name and department name info:



SELECT c.CompanyName, p.ParentAccName, s.SubAccName, d.DepartmentName
FROM
ParentAccount p left join
SubAccount s on s.ParentAccId = p.Pid,
Company c,
Department d

WHERE
p.Pid = d.DepartmentId AND p.Companyid = c.Cid



Im looking to get only distinct ParentAcc's per Company (Companies can have same ParentAccName) and the DepartmentName along with CompanyName. (The department here is the dept linked to the subaccounts per parent, this will only ever be one department)

mark renton

You will need to start with redefining your entity relationships

1 Company can have many Departments
1 Department can have many accounts
1 Account can have many subaccounts

Company - CID primary key
Department - DID primary key, CID foreign key
Account - AID primary key, DID foreign key
SubAccount - SAID primary key, AID foreign key