Need distinct values with SUM from multiple tables

Question

I'm building a Xojo app and have problems with a query I want to perform.

I need to export a instruction book that is saved in a database

It has 3 tables, one with the pages, one with the Items needed and a Item Database

For now I need to get the results from 2 tables

PartsPerPage and Parts

PartsPerPage

ID    -    SetID   -   Page   -   PartID   -   Parts
1     -    1       -   1      -   37878    -   5
2     -    1       -   1      -   23444    -   6
3     -    1       -   1      -   34534    -   11
4     -    1       -   2      -   37878    -   4

Parts

ID     -   Name   -   Size   -   Image   -   Stock   -   ColorID   -   SortCode
37878  -   Blabla -   6      -   picture -   56      -   12        -   box1
23444  -   Blabla -   9      -   picture -   12      -   11        -   box1
34534  -   Blabla -   3      -   picture -   66      -   101       -   box2
37878  -   Blabla -   2      -   picture -   33      -   4         -   box5

Now I want to get a list of all the Items that are used

ID     -  Parts  -  Name     -  Size   -  Image    -  ColorID
37878  -  9      -  Blabla   -  6      -  Picture  -  12
23444  -  6      -  Blabla   -  9      -  Picture  -  11  
34534  -  11     -  Blabla   -  3      -  Picture  -  101

I've tried some Sql queries but without any success

This one gave me only one record but should be like 155 records

SELECT DISTINCT 
     PartsPerPage.PartID, 
     SUM(PartsPerPage.Parts), 
     Parts.Name,
     Parts.Discription, 
     Parts.Size, Parts.image, 
     Parts.ColorID 
FROM PartsPerPage JOIN Parts ON Parts.ID = PartsPerPage.PartID 
WHERE PartsPerPage.SetID = 1

When I just do a simple query, Then I'll get 155 records if i do a record count, but not getting the results

SELECT DISTINCT 
     PartsPerPage.PartID
FROM PartsPerPage
WHERE PartsPerPage.SetID = 1

Any Help would be great

Answer 1

Presumably, you intend:

SELECT ppp.PartID, 
       SUM(PartsPerPage.Parts), 
       p.Name, p.Discription, p.Size, p.image, p.ColorID 
FROM PartsPerPage ppp JOIN
     Parts p
     ON p.ID = ppp.PartID 
WHERE ppp.SetID = 1
GROUP BY ppp.PartID, p.Name, p.Discription, p.Size, p.image, p.ColorID;

The GROUP BY is needed because you have a SUM(). The SUM() turns your query into an aggregation query -- with no GROUP BY, exactly one row is returned.

Answer 2

You are missing GROUP BY clause

SELECT
     PartsPerPage.PartID, 
     SUM(PartsPerPage.Parts), 
     Parts.Name,
     Parts.Discription, 
     Parts.Size, Parts.image, 
     Parts.ColorID 
FROM PartsPerPage JOIN Parts ON Parts.ID = PartsPerPage.PartID 
WHERE PartsPerPage.SetID = 1
GROUP BY PartsPerPage.PartID;