CODEWITHSUNDEEP
phpmysqlvb.net
AndrewTsang
AndrewTsang

Reputation: 316

MYsql union with exists statement

So i've got a fairly complicated sql statement merging ten tables into one table based on requirements.

A sample of the results (only consists of the essential required for this question)

Unique | date | amount | Table
12345  | 10/2 | 200    |   1
12345  | 10/2 |  20    |   2
23456  | 10/3 | 100    |   3

My question is, since the unique is the same for the first 2 rows, but from different tables, how can i merge them together so it becomes this?

Unique | date | amount | Table
12345  | 10/2 | 220    |   1 (Don't really care about the table number)
23456  | 10/3 | 100    |   3

This is a sample of my statement

(SELECT unique, dates, amt, tbl FROM ..... WHERE ....) 
UNION 
(SELECT unique, dates, amt, tbl FROM ..... WHERE ....) ORDER BY dates ASC

Would be great if it could be done via MYsql, if not, answers in PHP / VB.net would also be accepted. All help appreciated. Thanks

Upvotes: 0

Views: 403

Answers (1)

P.Salmon
P.Salmon

Reputation: 17640

You could wrap your query with a group by using a group_concat to grab all the table names. For example

DROP TABLE IF EXISTS T1,T2,T3;

CREATE TABLE T1(UNIQUEID  INT, DT DATE, AMT INT);
CREATE TABLE T2(UNIQUEID  INT, DT DATE, AMT INT);
CREATE TABLE T3(UNIQUEID  INT, DT DATE, AMT INT);

INSERT INTO T1 VALUES(1,'2017-01-01',10),(1,'2017-02-01',10),(2,'2017-01-01',20);
INSERT INTO T2 VALUES(1,'2017-01-01',10),(3,'2017-01-01',10),(4,'2017-01-01',20);
INSERT INTO T3 VALUES(2,'2017-01-01',10),(5,'2017-01-01',10),(4,'2017-01-01',20);

SELECT  UNIQUEID,DT,SUM(AMT) SUMAMT,SUBSTR(GROUP_CONCAT(TNAME),1,2)
FROM
(
SELECT 'T1' TNAME, UNIQUEID, DT,AMT FROM T1
UNION ALL
SELECT 'T2' TNAME, UNIQUEID, DT,AMT FROM T2
UNION ALL
SELECT 'T3' TNAME, UNIQUEID, DT,AMT FROM T3
) S
GROUP BY UNIQUEID,DT;

Result

+----------+------------+--------+---------------------------------+
| UNIQUEID | DT         | SUMAMT | SUBSTR(GROUP_CONCAT(TNAME),1,2) |
+----------+------------+--------+---------------------------------+
|        1 | 2017-01-01 |     20 | T1                              |
|        1 | 2017-02-01 |     10 | T1                              |
|        2 | 2017-01-01 |     30 | T1                              |
|        3 | 2017-01-01 |     10 | T2                              |
|        4 | 2017-01-01 |     40 | T3                              |
|        5 | 2017-01-01 |     10 | T3                              |
+----------+------------+--------+---------------------------------+
6 rows in set (0.00 sec)

The Group_concat can be ordered if you wish and if you leave out the substring then you could print all table names. It's not worth using a union statement without the all clause since mysql will attempt to dedupe and you don't really want to.

Upvotes: 1

Related Questions