CODEWITHSUNDEEP
sqlselectjoin
MikeP
MikeP

Reputation: 23

How to select all records in a table excluding records from another

I have 2 tables that hold information about pages and any categories that the pages belong to, the pages do not have to have any category and can also be in multiple categories.

page
 pageid      title
  1          page1
  2          page2
  3          page3
  4          page4

category
 pageid   category
  2       cat1
  2       obsolete
  3       cat2

I want to write a query that will select all pages as long as they are not categorised as 'obsolete'.

From the above, the output I am looking for is :

1     page1
3     page3
4     page4

So page 2 is excluded as one of its categories is 'obsolete' all other rows in the page table should be listed only once even if they have no categories or multiple categories in the category table.

I have tried multiple combinations of subquery and joins but cannot achieve the output I am looking to produce.

Upvotes: 1

Views: 144

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93694

Use NOT EXISTS

SELECT *
FROM   page p
WHERE  NOT EXISTS (SELECT pageid
                   FROM   category c
                   WHERE  p.pageid = c.pageid
                          AND category = 'obsolete');

Another way using Conditional Aggregate and EXISTS 

SELECT *
FROM   page p
WHERE  EXISTS (SELECT 1
               FROM   category c
               WHERE  p.pageid = c.pageid
               HAVING Count(CASE WHEN category = 'obsolete' THEN 1 END) = 0);

Upvotes: 2

ScaisEdge
ScaisEdge

Reputation: 133360

select * from page 
where pageid not in (select pageid from category where category='obsolete');

or 

 select distinct title from page 
 where pageid not in (select pageid from category where category='obsolete');

Upvotes: 0

Related Questions