0

My SQL isn't the best. Here's my situation:

My table consists of three fields. I'll call them A, B, and C (for simplicity).

For each A, there should be only one B-C combination. No matter how many times A is in the table, it should always have the same B and C. But, there are some duplicates where A will have a different C or even different Bs AND Cs, which I am trying to find.

Can anyone help me with my MySQL query?

4
Contributors
7
Replies
8
Views
6 Years
Discussion Span
Last Post by roachae
0
SELECT a, Count(a) AS CountOfa1  FROM (SELECT Table1.a, Table1.b, Table1.c, Count(Table1.a) AS CountOfa FROM Table1 GROUP BY Table1.a, Table1.b, Table1.c) GROUP BY a;

This will return the number of combinations of "b" and "c" for each "a"

0

No, he means his knowledge of SQL isn't the best, not the dbms called MySQL isn't the best. As we can tell from the lack of a primary key restriction on column A ;)
There should only be one row for each value of A. Full Stop. You should not allow a second row to be added.

Once you have identified and deleted the offending rows, alter the table to make column A the primary key.

0

Well if you added a unique compound key on A, B, C then it would prevent further rows being added that violates this rule. However that does not address the issue of the existing data that violates this rule.

0

Thanks, this pretty much did the trick. But now I'm trying to stick in a 'Where CountOfa1 > 1' clause, and I keep getting an error. I'm really only interested in data that's showing up more than once.

SELECT a, Count(a) AS CountOfa1  FROM (SELECT Table1.a, Table1.b, Table1.c, Count(Table1.a) AS CountOfa FROM Table1 GROUP BY Table1.a, Table1.b, Table1.c) GROUP BY a;

This will return the number of combinations of "b" and "c" for each "a"

0

Try a HAVING instead of a WHERE clause:

SELECT a, Count(a) AS CountOfa1  FROM (SELECT Table1.a, Table1.b, Table1.c, Count(Table1.a) AS CountOfa FROM Table1 GROUP BY Table1.a, Table1.b, Table1.c) GROUP BY a
HAVING CountOfa1 > 1;
0

Thanks! That worked great!

Try a HAVING instead of a WHERE clause:

SELECT a, Count(a) AS CountOfa1  FROM (SELECT Table1.a, Table1.b, Table1.c, Count(Table1.a) AS CountOfa FROM Table1 GROUP BY Table1.a, Table1.b, Table1.c) GROUP BY a
HAVING CountOfa1 > 1;
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.