Inner join with count on three tables

Question

Simple and fast question  i have those tables     table people   pe id   pe name     1    Foo      2    Bar      orders table   ord id   pe id   ord title         1        1     First order       2        2     Order two         3        2     Third order     items table   item id   ord id   pe id   title        1         1        1     Apple        2         1        1     Pear         3         2        2     Apple        4         3        2     Orange       5         3        2     Coke         6         3        2     Cake       I need to have a query listing all the people  counting the number of orders and the total number of items  like that     pe name   num orders   num items     Foo       1           2           Bar       2           4           But i can not make it work  I tried   SELECT     people pe name      COUNT orders ord id  AS num orders      COUNT items item id  AS num items FROM     people     INNER JOIN orders ON  orders pe id   people pe id      INNER JOIN items ON items pe id   people pe id GROUP BY     people pe id    But this returns the num   values incorrect     name   num orders   num items     Foo       2           2           Bar       8           8           I noticed that if i try to join one table at time  it works   SELECT     people pe name      COUNT orders ord id  AS num orders FROM     people     INNER JOIN orders ON  orders pe id   people pe id  GROUP BY     people pe id     give me    pe name   num orders     Foo                1     Bar                2      and  SELECT     people pe name      COUNT items item id  AS num items FROM     people     INNER JOIN items ON  items pe id   people pe id  GROUP BY     people pe id    output    pe name   num items     Foo               2     Bar               4     How to combine those two queries in one

User · Answer

i tried putting distinct on both   count distinct ord ord id  as num order  count distinct items item id  as num items  its working         SELECT          people pe name           COUNT distinct orders ord id  AS num orders           COUNT distinct items item id  AS num items     FROM          people          INNER JOIN orders ON  orders pe id   people pe id           INNER JOIN items ON items pe id   people pe id     GROUP BY          people pe id    Thanks for the Thread it helps

User · Answer

As Frank pointed out  you need to use DISTINCT  Also  since you are using composite primary keys  which is perfectly fine  BTW  you need to make sure that you use the whole key in your joins   SELECT     P pe name      COUNT DISTINCT O ord id  AS num orders      COUNT I item id  AS num items FROM     People P INNER JOIN Orders O ON     O pe id   P pe id INNER JOIN Items I ON     I ord id   O ord id AND     I pe id   O pe id GROUP BY     P pe name   Without I ord id   O ord id it was joining each item row to every order row for a person

User · Answer

Your solution is nearly correct  You could add DISTINCT   SELECT     people pe name      COUNT distinct orders ord id  AS num orders      COUNT items item id  AS num items FROM     people     INNER JOIN orders ON  orders pe id   people pe id      INNER JOIN items ON items pe id   people pe id GROUP BY     people pe id

User · Answer

It makes more sense to join the item with the orders than with the people    SELECT     people pe name      COUNT distinct orders ord id  AS num orders      COUNT items item id  AS num items FROM     people     INNER JOIN orders ON orders pe id   people pe id          INNER JOIN items ON items ord id   orders ord id GROUP BY     people pe id    Joining the items with the people provokes a lot of doublons   For example  the cake items in order 3 will be linked with the order 2 via the join between the people  and you don t want this to happen     So    1- You need a good understanding of your schema  Items are link to orders  and not to people    2- You need to count distinct orders for one person  else you will count as many items as orders

User · Answer

One needs to understand what a JOIN or a series of JOINs does to a set of data  With strae s post  a pe id of 1 joined with corresponding order and items on pe id   1 will give you the following data to  select  from        table people portion     table orders portion     table items portion       people pe id   people pe name   orders ord id   orders pe id   orders ord title   item item id   item ord id   item pe id   item title       1   Foo   1   1   First order   1   1   1   Apple       1   Foo   1   1   First order   2   1   1   Pear     The joins essentially come up with a cartesian product of all the tables  You basically have that data set to select from and that s why you need a distinct count on orders ord id and items item id  Otherwise both counts will result in 2 - because you effectively have 2 rows to select from

User · Answer

select pe name count  distinct b ord id  count c item id    from people  a  order1 as b  item as c  where a pe id b pe id and b ord id c order id   group by a pe id pe name

[sql] Inner join with count() on three tables

Examples related to sql

Examples related to join

Examples related to count