I have two tables, website and page, that look like such:
website
id | name
------------
1 | site 1
2 | site 2
3 | site 3
page
id | name | site_id | deleted
----------------------------------
1 | home| 1 | 0
2 | pg2 | 1 | 1
3 | pg1 | 2 | 0
4 | pg2 | 2 | 0
5 | pg1 | 3 | 1
6 | this| 3 | 1
In the example, site 1 has one deleted page and one active page; site 2 has two active pages; site 3 has no active pages. The goal is to return a list of websites that have no actives pages.
I have the following query that gives me the number of deleted pages for each site:
SELECT w.id, w.name, count(p.id) AS deleted_pages_num
FROM page p
RIGHT JOIN website w ON p.site_id = w.id
WHERE p.deleted = 1
GROUP BY p.site_id
Where you'd get this result
id| name | deleted_pages_num
-------------------------------
1 | site 1 | 1
2 | site 2 | 0
3 | site 3 | 2
and vice versa where you'd set p.is_deleted = 0 to get the number of active pages each website has. I need to find the difference between the two queries so that I can identify which sites have had all pages deleted. Just totally blanking on this.
Best Answer
Try to use the following:
Probably this is what you're asking for