CODEWITHSUNDEEP
mysqlsql
ali
ali

Reputation: 115

SQL search values with same mutiple column

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 ?

Upvotes: 0

Views: 122

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520928

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

Upvotes: 3

Related Questions