0

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.

1

Try to use the following:

select * from website a where not exists (select null from page where site_id=a.id and deleted=0)

Probably this is what you're asking for

| improve this answer | |
  • 1
    EXISTS and NOT EXISTS! I can't remember the last time I used either. Utterly and completely forgot about them. Thank you, this did it. – MP12389 Jan 13 at 21:10
  • Enjoy :-) and thank you for +15 of reputation :-D – Alexander Gavriliuk Jan 13 at 21:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.