Getting duplicate count and data from table

Question

I have 3 tables like this:

table_1

+-ID-+-table_3_id-+
|  1 |          1 |
|  2 |          1 |
|  3 |          2 |
+----+------------+

table_2

+-ID-+-table_1_id-+-name--+-value-+
|  1 |          1 | Name1 | Data1 |
|  2 |          1 | Name2 | Data2 |
|  3 |          1 | Name3 | Data3 |
|  4 |          2 | Name1 | Data1 |
|  5 |          2 | Name2 | Data4 |
|  6 |          2 | Name3 | Data5 |
|  7 |          3 | Name1 | Data6 |
|  8 |          3 | Name2 | Data2 |
+----+------------+-------+-------+

table 3 consists of IDs and other data that is irrelevant to this question. However, I need to be able to filter on table_3_id.

This is what I need: Table 2 has multiple rows that have information of the rows in table_1. I need to have a query that checks if there are duplicates in the 'data' column, which have the same 'name'. The result I need would be this (with a WHERE table_3_id = 1):

+-table_3_id-+-name--+-value-+-duplicate-+
|          1 | Name1 | Data1 | true      |
|          1 | Name2 | Data2 | false     |
|          1 | Name3 | Data3 | false     |
+------------+-------+-------+-----------+

Or, if possible, only return the data from table_2 where it actually is a duplicate. The 'duplicate' field can be a count too, since I do know the amount of rows that have the same table_3_id.

I hope I have made my question clear enough. If it is not clear enough I will try to improve it. I have tried it with joins and subqueries, but my knowledge of SQL isn't enough to make an advanced query like this. I'd prefer this in a single query instead of multiple in PHP.

Anthony Horne · Accepted Answer · 2012-11-05 08:34:57Z

2

You can use something along the lines of the below:

select Name
from table
group by name
having count(*) > 1

answered Nov 5, 2012 at 8:34

Anthony Horne

2,5202 gold badges31 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Deep Frozen Over a year ago

Thanks! Never even thought of the having. It works like a charm!

Treb · Accepted Answer · 2012-11-05 08:52:00Z

1

You need to check for every row in Table_2, if there are duplicates. This can be done with a subquery, like this:

SELECT 
    B.Table_3_ID
    ,A.Name 
    ,A.Value
    ,(SELECT COUNT(*) FROM Table_2 C
        WHERE C.Name = A.Name AND C.Value = A.Value)
    AS DuplicateCount
FROM Table_2 A
INNER JOIN Table_1 B
    ON A.Table_1.ID = B.ID

There may be some errors in my SQL, especially the table aliases, because I have no syntax check at the moment, but the principle should work.

answered Nov 5, 2012 at 8:52

Treb

20.3k8 gold badges60 silver badges88 bronze badges

Collectives™ on Stack Overflow

Getting duplicate count and data from table

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related