SQL search values with same mutiple column

Question

I am new in SQL. I would like to have a query that returns values which have multiple columns in common

I have t_table with:

           filename               |    start            |    stop
 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA | 2016-12-24 00:00:00 | 2016-01-03 00:00:00
 AAAAAAAAAAAAAAAABBBBBBBBBBBBBBBB | 2016-12-24 00:00:00 | 2016-01-03 00:00:00
 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC | 2016-12-24 00:00:00 | 2016-01-03 00:00:00

I would like to return filenames with start and stop columns in common and name is like '%AAAA%' (2 first rows) CCCC% has the same start and stop but name isn't like '%AAAA%

I've tried with these answer Find rows that have the same value on a column in MySQL but I couldn't get the result expected. With :

SELECT  filename ,
        start
FROM    t_table
WHERE   ( start IN ( SELECT start
                     FROM   t_table
                     GROUP BY start
                     HAVING COUNT(*) > 1 ) )
        AND ( filename LIKE '%AAAA%' );

I've obtained all of them ...

           filename               |    start            |    stop
 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA | 2016-12-24 00:00:00 | 2016-01-03 00:00:00
 AAAAAAAAAAAAAAAABBBBBBBBBBBBBBBB | 2016-12-24 00:00:00 | 2016-01-03 00:00:00
 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC | 2016-12-24 00:00:00 | 2016-01-03 00:00:00

Instead of :

           filename               |    start            |    stop
 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA | 2016-12-24 00:00:00 | 2016-01-03 00:00:00
 AAAAAAAAAAAAAAAABBBBBBBBBBBBBBBB | 2016-12-24 00:00:00 | 2016-01-03 00:00:00

Could you please help me ?

You should show more sample data along with expected output, but in any case I attempted an answer below. — Tim Biegeleisen, Sep 15 '16 at 08:17

Tim Biegeleisen · Accepted Answer · 2016-09-15T08:40:13.063

3

SELECT t1.filename,
       t1.start,
       t1.stop
FROM t_table t1
INNER JOIN
(
    SELECT start, stop            -- this subquery identifies all start, stop
    FROM t_table                  -- pairs for which 2 or more records share
    GROUP BY start, stop          -- those values
    HAVING COUNT(*) > 1
) t2
    ON t1.start = t2.start AND
       t1.stop  = t2.stop
WHERE t1.filename LIKE '%AAAA%'   -- filename contains 'AAAA'

Demo here:

SQLFiddle

edited Sep 15 '16 at 08:40

answered Sep 15 '16 at 08:13

Tim Biegeleisen

502,043
27
286
360

@ali Please check the Fiddle. I only get two records from my query, not three. – Tim Biegeleisen Sep 15 '16 at 08:40

SQL search values with same mutiple column

1 Answers1

SQLFiddle