Removing duplicates from result of multiple join on tables with different columns in MySQL

Question

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.

Answer 1

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

Answer 2

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;

Answer 3

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;

Answer 4

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.

Answer 5

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