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
– Tim Biegeleisen, Commented Sep 15, 2016 at 8:17

Tim Biegeleisen · Accepted Answer · 2016-09-15 08:40:13Z

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, 2016 at 8:40

answered Sep 15, 2016 at 8:13

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

1 Comment

Tim Biegeleisen Over a year ago

@ali Please check the Fiddle. I only get two records from my query, not three.

Collectives™ on Stack Overflow

SQL search values with same mutiple column

1 Answer 1

SQLFiddle

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related