Removing duplicates from result of multiple join on tables with different columns in MySQL
I am trying to make one statement to pull data from 3 related tables (as in they all share a common string index). I am having trouble preventing MySQL from returning the product of two of the tables, making the result set much larger than I want it. Each table has a different number of columns, and I would prefer to not use UNION anyway, because the data in each table is separate.
Here is an example:
Table X is the main table and has fields A B.
Table Y has fields A C D.
Table Z has fields A E F G.
-
My ideal result would have the form:
A1 B1 C1 D1 E1 F1 G1
A1 B2 C2 D2 00 00 00
A2 B3 C3 D3 E2 F2 G2
A2 B4 00 00 E3 F3 G3
etc...
-
Here is the simplest SQL I have tried that shows my problem (that is, it returns the product of Y * Z indexed by data from A:
SELECT DISTINCT *
FROM X
LEFT JOIN Y USING (A)
LEFT JOIN Z USING (A)
-
I have tried adding a group by clause to fields on Y and Z. But, if I only group by one column, it only returns the first result matched with each unique value in that column (ie: A1 C1 E1, A1 C2 E1, A1 C3 E1). And if I group by two columns it returns the product of the two tables again.
I've also tried doing multiple select statements in the query, then joining the resulting tables, but I received the product of the tables as output again.
Basically I want to merge the results of three select statements into a single result, without it giving me all combinations of the data. If I need to, I can resort to doing multiple queries. However, since they all contain a common 开发者_运维问答index, I feel there should be a way to do it in one query that I am missing.
Thanks for any help.
I don't know if I understand your problem, but why are you using a LEFT JOIN? The story sounds more like an INNER JOIN. Nothing here calls for a UNION.
[Edit] OK, I think I see what you want now. I've never tried what I am about to suggest, and what's more, some DBs don't support it (yet), but I think you want a windowing function.
WITH Y2 AS (SELECT Y.*, ROW_NUMBER() OVER (PARTITION BY A) AS YROW FROM Y),
Z2 AS (SELECT Z.*, ROW_NUMBER() OVER (PARTITION BY A) AS ZROW FROM Z)
SELECT COALESCE(Y2.A,Z2.A) AS A, Y2.C, Y2.D, Z2.E, Z2.F, Z2.G
FROM Y2 FULL OUTER JOIN Z2 ON Y2.A=Z2.A AND YROW=ZROW;
The idea is to print the list in as few rows as possible, right? So if A1 has 10 entries in Y and 7 in Z, then we get 10 rows with 3 having NULLs for the Z fields. This works in Postgres. I do not believe this syntax is available in MySQL.
Y:
a | d | c
---+---+----
1 | 1 | -1
1 | 2 | -1
2 | 0 | -1
Z:
a | f | g | e
---+---+---+---
1 | 9 | 9 | 0
2 | 1 | 1 | 0
3 | 0 | 1 | 0
Output of statement above:
a | c | d | e | f | g
---+----+---+---+---+---
1 | -1 | 1 | 0 | 9 | 9
1 | -1 | 2 | | |
2 | -1 | 0 | 0 | 1 | 1
3 | | | 0 | 0 | 1
Yep, UNION
is not the answer.
I'm thinking you want:
SELECT *
FROM x
JOIN y ON x.a = y.a
JOIN z ON x.a = z.a
GROUB BY x.a;
I found a new way editing this post and this can be used to merg two table
according to unique ids.
Try this:
create table y
(
a int,
d int,
c int
)
create table z
(
a int,
f int,
g int,
e int
)
go
insert into y values(1,1,-1)
insert into y values(1,2,-1)
insert into y values(2,0,-1)
insert into z values(1,9,9,0)
insert into z values(2,1,1,0)
insert into z values(3,0,1,0)
go
select * from y
select * from z
WITH Y2 AS (SELECT Y.*, ROW_NUMBER() OVER (ORDER BY A) AS YROW FROM Y where A = 3),
Z2 AS (SELECT Z.*, ROW_NUMBER() OVER (ORDER BY A) AS ZROW FROM Z where A = 3)
SELECT COALESCE(Y2.A,Z2.A) AS A, Y2.C, Y2.D, Z2.E, Z2.F, Z2.G
FROM Y2 FULL OUTER JOIN Z2 ON Y2.A=Z2.A AND YROW=ZROW;
PostgreSQL is always the right answer to most MySQL issues, but your problem could have been solved this way :
The issue you experienced was that you had two left joins, i.e.
A left join X left join Y which inevitably gives you A x X x Y where you wanted (AxX)x(AxY)
A simple solution could be :
select x.A,x.B,x.C,x.D,y.E,y.F,y.G from (SELECT A.A,A.B,X.C,X.D FROM A LEFT JOIN X ON A.A=X.A) x INNER JOIN (SELECT A.A,Y.E,Y.F,Y.G FROM A LEFT JOIN Y ON A.A=Y.A) y ON x.A=y.A
For the test details :
CREATE TABLE A (A varchar(3),B varchar(3));
CREATE TABLE X (A varchar(3),C varchar(3), D varchar(3));
CREATE TABLE Y (A varchar(3),E varchar(3), F varchar(3), G varchar(3));
INSERT INTO A(A,B) VALUES ('A1','B1'), ('A2','B2'), ('A3','B3'), ('A4','B4');
INSERT INTO X(A,C,D) VALUES ('A1','C1','D1'), ('A3','C3','D3'), ('A4','C4','D4');
INSERT INTO Y(A,E,F,G) VALUES ('A1','E1','F1','G1'), ('A2','E2','F2','G2'), ('A4','E4','F4','G4');
select x.A,x.B,x.C,x.D,y.E,y.F,y.G from (SELECT A.A,A.B,X.C,X.D FROM A LEFT JOIN X ON A.A=X.A) x INNER JOIN (SELECT A.A,Y.E,Y.F,Y.G FROM A LEFT JOIN Y ON A.A=Y.A) y ON x.A=y.A
As a summary, yes MySQL has many many many issues, but this is not one of them - most of the issues concern more advanced stuff.
If I understand correctly, table X
has a 1:n
relationship with both tables Y
and Z
. So, the behaviour you see is expected. The result you get is a kind of Cross Product.
If X
has Person data, Y
has Address data for those persons and Z
has Phone data for those persons, then it's natural your query to show all combinations of addresses and phones for every person. If someone has 3 addresses and 4 phones in your tables, then the query shows 12 rows in the result.
You could avoid it by either using a UNION
query or issuing two queries:
SELECT X.*
, Y.*
FROM X
LEFT JOIN Y
ON Y.A = X.A
and:
SELECT X.*
, Z.*
FROM X
LEFT JOIN Z
ON Z.A = X.A
精彩评论