Here i am again :)
I have 5 tables:
customers id - name
p_orders id - id_customer - code - date
p_items id - id_order - description - price
and h_orders and h_items, that are exactly the copy of p_orders and p_items.
When the p_ tables reach a big amount of rows, i move the oldest to the h_ tables.. they due as history.
So, my problem is: how to retrieve the data from both the p_ tables and h_ considering them as one unique table?
For example, i want to retrieve the number of orders for each customer, and the total price (of all the customer's orders), and i use that query:
SELECT
customer.id,
customer.name,
count(DISTINCT p_orders.id) AS num_orders,
sum(p_items.price) AS total_money
FROM
customer
INNER JOIN p_orders ON p_orders.id_customer = customer.id
INNER JOIN p_items ON p_items.id_order = p_orders.id
GROUP BY
customer.id,
customer.name,
p_orders.id_customer
ORDER BY
customer.id
it works just for one 'set' of tables (p_ or h_)..but i want them both.
I've tryed to use an UNION:
(
SELECT
customer.id,
customer.name,
count(DISTINCT p_orders.id) AS num_orders,
sum(p_items.price) AS total_money
FROM
customer
INNER JOIN p_orders ON p_orders.id_customer = customer.id
INNER JOIN p_items ON p_items.id_order = p_orders.id
GROUP BY
customer.id,
customer.name,
p_orders.id_customer
)
UNION
(
SELECT
customer.id,
customer.name,
count(DISTINCT h_orders.id) AS num_orders,
sum(h_items.price) AS total_money
FROM
customer
INNER JOIN h_orders ON h_orders.id_customer = customer.id
INNER JOIN h_items ON h_items.id_order = h_orders.id
GROUP BY
customer.id,
customer.name,
h_orders.id_customer
)
ORDER BY id ASC
This one works, but if a customer have orders both in the p_ tables and in the h_ tables, i'll have 2 rows for that customer with 2 different num_orders and total_money (respectively coming from p_ tables and h_ tables)
I've tryed to add a GROUP BY id outside the union:
(
--SELECT 2
)
UNION
(
--SELECT 1
)
GROUP BY id
ORDER BY id ASC
but the query fail with ERROR: syntax error at or near "GROUP" at character 948, seem like GROUP BY cannot be used in that way.
Any suggestion?
EDIT:
For uriDium, yes, all the tables have the id column as primary key, and the referred fields (aka p_orders.id_customer) are foreign keys too. Here the test db structure dump (i added some indexes and foreign keys after the table creation, but i dont think that this mean something):
CREATE TABLE customer (
id serial NOT NULL,
name character(50)
);
CREATE TABLE p_orders (
id serial NOT NULL,
id_customer integer NOT NULL,
date date DEFAULT now(),
code character(5)
);
CREATE TABLE p_items (
id serial NOT NULL,
id_order integer NOT NULL,
descr character(250),
price money
);
CREATE TABLE h_orders (
id integer NOT NULL,
id_customer integer NOT NULL,
date date,
code character(5)
);
CREATE TABLE h_items (
id integer NOT NULL,
id_order integer NOT NULL,
descr character(250),
price money
);
CREATE UNIQUE INDEX id_h_orders ON h_orders USING btree (id);
CREATE INDEX id_h_o_c ON h_orders USING btree (id_customer);
CREATE UNIQUE INDEX id_items_h ON h_items USING btree (id);
CREATE INDEX id_ordinr_dsve ON h_items USING btree (id_order);
ALTER TABLE ONLY customer
ADD CONSTRAINT customer_pkey (id);
ALTER TABLE ONLY p_orders
ADD CONSTRAINT p_orders_pkey PRIMARY KEY (id);
ALTER TABLE ONLY p_items
ADD CONSTRAINT p_items_pkey PRIMARY KEY (id);
ALTER TABLE ONLY stats
ADD CONSTRAINT stats_pkey PRIMARY KEY (id);
ALTER TABLE ONLY p_orders
ADD CONSTRAINT "$1" FOREIGN KEY (id_customer) REFERENCES customer(id) ON DELETE CASCADE;
ALTER TABLE ONLY p_items
ADD CONSTRAINT "$1" FOREIGN KEY (id_order) REFERENCES p_orders(id) ON DELETE CASCADE;
ALTER TABLE ONLY h_orders
ADD CONSTRAINT "$1" FOREIGN KEY (id_customer) REFERENCES customer(id) ON DELETE CASCADE;
ALTER TABLE ONLY h_items
ADD CONSTRAINT "$1" FOREIGN KEY (id_order) REFERENCES h_orders(id) ON DELETE CASCADE;