Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have two tables

1st Table : center (2 columns)

+-----------------------------------+
|id | company_name                  |
+-----------------------------------+ 
|2  | Skytel Outsourcing Pvt. Ltd.  |
+-----------------------------------+

2nd Table : campaign_center (3 columns)

+-------------------------------------+
|campaign_id |  Center_id  | sl_no    |
+-------------------------------------+ 
|Demo        |    2        | 2        |
+-------------------------------------+
|MyFootPath  |    2        | 3        |
+-------------------------------------+

When I execute this query it's working perfectly: (To find center_id and company name who's campaignid is 'Demo')

select center.id, center.company_name 
from center 
  inner join campaign_center 
  on center.id = campaign_center.center_id 
where campaign_center.campaign_id = 'Demo'

but again when i am trying to find those company who's campaign_id is not 'Demo' it doesn't give correct output:

select center.id, center.company_name 
from center 
  inner join campaign_center 
  on center.id = campaign_center.center_id 
where campaign_center.campaign_id != 'Demo'

How do I get those ? Please help.

share|improve this question
    
From what I can see, the second query output is entirely correct. –  mustaccio Oct 22 '13 at 18:00
add comment

1 Answer

up vote 0 down vote accepted

Your second query will give you all companies that have (one or more) campaign centers with id different than 'Demo'. But those that have a 'Demo' and some other campaign id will still be included.

I guess that you want something different. To find those companies that none of their campaign_id is 'Demo'. If so, you need an anti-semijoin (often called anti-join) which can be accomplished with either NOT IN or a LEFT JOIN / IS NULL or a NOT EXISTS query:

SELECT c.id, c.company_name 
FROM center AS c
WHERE NOT EXISTS
      ( SELECT 1
        FROM campaign_center AS cc 
        WHERE c.id = cc.center_id 
          AND cc.campaign_id = 'Demo'
      ) ;
share|improve this answer
    
Thank you sir .. its working –  Arka Bhattacharjee Oct 22 '13 at 18:14
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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