MYSQL statement: finding the number of records, how many times in another table

Question

I have two database tables:

Table 1:

+---------+-------+-------------+
| Page    | Title | Description |
+---------+-------+-------------+
| Apple   | ..... | ........... |
| Orange  | ..... | ........... |
| Pear    | ..... | ........... |
| Grapes  | ..... | ........... |
+---------+-------+-------------+

Table 2:

+----------+-------------+
|   Link   |    Page     |
+----------+-------------+
| Website1 |    Apple    |
| Website2 |    Orange   |
| Website3 |    Apple    |
| Website4 |    Orange   |
| Website5 |    Apple    |
| Website6 |    Pear     |
| Website7 |    Apple    |
| Website8 |    Grapes   |
| Website9 |    Grapes   |
+----------+-------------+

I want to know / return how many pages from table 1 are listed in table 2 and how many times they link. (I DO NOT want to know how many times EVERY page in table 1 is listed in table 2).

So, in this example: 1 page links 1 time (Pear), 2 pages link 2 times (Grapes and Orange) & 1 page links 4 times.

Which SQL statement would I use for this?

Answer 1

The following query should do.

SELECT COUNT(1) NoOfPages,CNT ReferencedTimes
FROM
(
    SELECT T2.PAGE,COUNT(1) CNT
    FROM TABLE1 T1 INNER JOIN TABLE2 T2 ON T1.PAGE = T2.PAGE
    GROUP BY T2.PAGE
)T
GROUP BY CNT

Answer 2

I think the following statement will match:

SELECT count(*) FROM Table2 WHERE (Table2.Page IN (SELECT Page FROM Table1));

Answer 3

 SELECT (GROUP_CONCAT(DISTINCT page)) AS Page,page_count 
      FROM
          (SELECT table1.Page as page,COUNT(*) as page_count
           FROM table1 INNER JOIN table2 ON table1.Page=table2.Page
          GROUP BY table1.Page)
       as T GROUP BY page_count

,

Answer 4

Select table2.page,cnt(table2.page) 
                          from table1 inner join table2 
                        On table1.Page=table2.Page group by table2.page

Answer 5

If what you are looking for is an X page that has been referenced N times, below the request will achieve this:

SELECT COUNT(t1.page), t2.count
FROM table1 t1
INNER JOIN (SELECT page,COUNT(*) AS count FROM table2 GROUP BY page) t2 ON t1.page=t2.page
GROUP BY t2.count

Answer 6

Try this query, it will make a left join and tell you how many times the element is referenced in table2 if the counter is zero than the link in another table

SELECT table1.Page, count(table2.Page) as count
FROM table1
LEFT JOIN table2 ON table2.Page = table1.Page
GROUP BY table1.Page