CODEWITHSUNDEEP
sql
jmcclane
jmcclane

Reputation: 189

SQL Query over 5 tables

I have four tables in a model. These tables are:

Clients --> client_id, name
User --> user_id;user_name,client_id (FK Clients),
Products --> product_id;product_name,client_id (FK Clients)
Orders --> order_id;user_id (FK User),
OrderPos --> order_pos_id;order_id (FK Orders);quantity;product_id (FK Products)

That means, that different clients have different products and every user who belongs to one client can order those products who also belongs to this client.

How the query has to be, to get the results in the following way (per Client)?

User | ProduktnameX | ProduktnameXY | ProduktnameXYZ | ...
Tim  | 0            | 12            | 5
Tom  | 0            | 0             | 0
Jim  | 1            | 23            | 4

I want to display all Products per Client and all Users per Client. Then in this list I want to display which User has ordered which amount of each of the available products (from Orders)...

Upvotes: 0

Views: 75

Answers (1)

Rob Farley
Rob Farley

Reputation: 15849

SELECT c.client_id, c.name, p.product_id, p.product_name, sum(op.quantity) as product_quantity
FROM Clients AS c
JOIN User AS u ON u.client_id = c.client_id
JOIN Orders AS o ON o.user_id = u.user_id
JOIN OrderPos AS op ON op.order_id = o.order_id
JOIN Products AS p ON p.product_id = op.product_id
GROUP BY c.client_id, c.name, p.product_id, p.product_name;

Then use a reporting tool (such as SSRS or whatever), to show a matrix/cross tab/pivot, grouping by product_id and product_name (showing the name) on the columns and similarly for clients on the rows, and product_quantity in the middle.

Upvotes: 1

Related Questions