MySQL - data selection where the counter is greater than 1

Question

I have the following table:

+-------------------------------------+----------------------------------------------------------------+
| keyword                             | landing_page                                                   |
+-------------------------------------+----------------------------------------------------------------+
| orange                              | https://www.example.co.uk/                               |
| apple                               | https://www.example.co.uk/                               |
| pear                                | https://www.example.co.uk/                               |
| apple                               | https://www.example.co.uk/                               |
| apple                               | https://www.example.co.uk/landing-page |
+-------------------------------------+----------------------------------------------------------------+

I would like to select any keyword with several separate landing pages, so in this example we will return:

apple, https://www.example.co.uk
apple, https://www.example.co.uk/landing-page

How can I do this with MySQL?

UPDATE : I tried the following, but it did not work:

select keyword, count(landing_page) 
from search_data 
group by keyword 
having count(distinct landing_page) > 1;

Answer 1

The request you requested is part of the solution. Use this query as an inline view to identify keywords that contain more than one landing page. Attach the result of this query to the source table.

SELECT t.keyword
     , t.landing_page
 FROM ( -- keyword with more than one landing page 
        SELECT r.keyword
          FROM search_data r 
         GROUP BY r.keyword
        HAVING COUNT(DISTINCT r.landing_page) > 1
      ) s
 JOIN search_data t
   ON t.keyword = s.keyword
GROUP BY t.keyword, t.landing_page
ORDER BY t.keyword, t.landing_page

. , . , , _:

SELECT DISTINCT t.keyword, t.landing_page
  FROM search_data t
 WHERE EXISTS ( SELECT 1
                  FROM search_data r
                 WHERE r.keyword = t.keyword
                   AND NOT ( r.landing_page <=> t.landing_page )
              )
 ORDER BY t.keyword, t.landing_page

Followup

:

CREATE TABLE search_data (keyword VARCHAR(10), landing_page VARCHAR(80))
;
CREATE INDEX search_data_IX1 ON search_data (keyword, landing_page)
;
INSERT INTO search_data (keyword, landing_page) VALUES
 ('orange','https://www.example.co.uk/')
,('apple','https://www.example.co.uk/')
,('pear','https://www.example.co.uk/')
,('apple','https://www.example.co.uk/')
,('apple','https://www.example.co.uk/landing-page')
;

EXPLAIN 1

EXPLAIN    
SELECT t.keyword
     , t.landing_page
 FROM ( -- keyword with more than one landing page 
        SELECT r.keyword
          FROM search_data r 
         GROUP BY r.keyword
        HAVING COUNT(DISTINCT r.landing_page) > 1
      ) s
 JOIN search_data t
   ON t.keyword = s.keyword
GROUP BY t.keyword, t.landing_page
ORDER BY t.keyword, t.landing_page

--     id  select_type  table       type    possible_keys    key              key_len  ref       rows  Extra
-- ------  -----------  ----------  ------  ---------------  ---------------  -------  ------  ------  ------------------------
--      1  PRIMARY      <derived2>  system  (NULL)           (NULL)           (NULL)   (NULL)       1  
--      1  PRIMARY      t           ref     search_data_IX1  search_data_IX1  13       const        2  Using where; Using index
--      2  DERIVED      r           index   (NULL)           search_data_IX1  96       (NULL)       5  Using index

1

SELECT t.keyword
     , t.landing_page
 FROM ( -- keyword with more than one landing page 
        SELECT r.keyword
          FROM search_data r 
         GROUP BY r.keyword
        HAVING COUNT(DISTINCT r.landing_page) > 1
      ) s
 JOIN search_data t
   ON t.keyword = s.keyword
GROUP BY t.keyword, t.landing_page
ORDER BY t.keyword, t.landing_page

-- keyword  landing_page
-- -------  --------------------------------------
-- apple    https://www.example.co.uk/
-- apple    https://www.example.co.uk/landing-page

2

EXPLAIN
SELECT DISTINCT t.keyword, t.landing_page
  FROM search_data t
 WHERE EXISTS ( SELECT 1
                  FROM search_data r
                 WHERE r.keyword = t.keyword
                   AND NOT ( r.landing_page <=> t.landing_page )
              )
 ORDER BY t.keyword, t.landing_page

--     id  select_type         table   type    possible_keys    key              key_len  ref               rows  Extra
-- ------  ------------------  ------  ------  ---------------  ---------------  -------  --------------  ------  -------------------------------------
--      1  PRIMARY             t       range   (NULL)           search_data_IX1  96       (NULL)               6  Using where; Using index for group-by
--      2  DEPENDENT SUBQUERY  r       ref     search_data_IX1  search_data_IX1  13       test.t.keyword       1  Using where; Using index

2

SELECT DISTINCT t.keyword, t.landing_page
  FROM search_data t
 WHERE EXISTS ( SELECT 1
                  FROM search_data r
                 WHERE r.keyword = t.keyword
                   AND NOT ( r.landing_page <=> t.landing_page )
              )
 ORDER BY t.keyword, t.landing_page

-- keyword  landing_page
-- -------  --------------------------------------
-- apple    https://www.example.co.uk/
-- apple    https://www.example.co.uk/landing-page