I am trying to learn SQL and I wrote the following query to combine 4 different tables in MC to get Accounts with orders and respective product names (Id is the primary key from Account and [Account Name ] is the foreign key in Orders object, also Id is the primary key in Orders object and OrderId is foreign key in Order items object and so on.. ) . I am getting an error "Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object 'target DE name' . I looked for solution and I saw people suggesting to add second primary key in the target DE which is not working for me and another one is deduplicating which I don not want as I want to see all orders placed by single Account . When I run the below query 55 records gets returned and soon after that it becomes 0 due to the error . Please suggest a solution . Thank you in advance .
SELECT
A.[Customer Points],
A.CreatedById,
[Customer Subscribed to NL],
FirebaseToken__pc,
A.[Customer First Name],
A.Gender,
A.Id,
[Customer Confirmed Email],
A.LastModifiedById,
A.[Customer Last Name],
MasterRecordId,
A.OwnerId,
A.ParentId,
[Customer ID],
[Date of Birth],
A.PersonContactId,
A.Email,
A.Country,
A.[Country Code],
[Customer Type],
P.[Product Name],
P.[Product Category],
O.[Order Number],
O.[Account Name],
O.Id AS OrderId,
OI.[Real Price],
OI.[Quantity]
FROM Account A
INNER JOIN [Order] O
ON A.Id = O.[Account Name]
INNER JOIN OrderItem OI
ON OI.OrderId = O.Id
INNER JOIN Product2 P
ON OI.Product2Id = P.Id
Mateusz Dąbrowski (mateuszdabrowski.pl) 
Forum Ambassador
If there is 1 account to many orders and 1 order to many orderItems and 1 orderItem to many Product2 then yes, you need all those to be Primary Key and in the result you will have multiple records for the same Account.
