• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 266
  • Last Modified:

Can't figure out how to build an sqlquery with negation.

Situation:
I'm developing a microsoft windows program in vb.net with a "search for information screen".
All data is stored in an sqldatabase and all data is about persons, contactinformation and perks.

Tables:
PERSON with columns ID, FIRSTNAME, LASTNAME, ...
(example person: 1 Joe Johnson, 2 Sam Salmon, 3 Fred Frederico
PERK with columns ID, PERKNAME, ...
(example skills: 1 Group Insurance, 2 Healt Insurance, 3 Car)
PERK_TO_PERSON with columns id, personid, perkid
Persons can have multiple perks, perks can be assigned to multiple persons.
(example: Joe can have Group and a Car. Sam can have Group and Health. Fred doesn't have a perk => so no entry for him in PERK_TO_PERSON)

Problem:
Currently all searches are pretty straightforward (select personid from perk_to_person where perkid = 2)
My boss now wants to have the ability to search on not. Example: The program must be able to extract all personsids that don't have healt insurance. In this case that would be 1 and 3.

Anyone who can help me build the querystring for that?
0
TiTANS_C
Asked:
TiTANS_C
  • 2
  • 2
1 Solution
 
Philip PinnellCommented:
select personid from perk_to_person where perkid <> 2
0
 
Philip PinnellCommented:
Is that what you want or do I misunderstand?
0
 
TiTANS_CAuthor Commented:
That is partially what i want.

The query you build will give me Joe.
But not Fred (who doesn't have an enty in perk_to_person)

The full personidlist must be checked.

(I'm so bad at explaining things, sorry if it isn't clear)

The list of entries for perk_to_person are:
id     persid     perkid  
1     1              1    
2     1              3
3     2              1
4     2              2

This is an incomplete query I build but maybe it can help you guys think in the right direction:

select distinct(persid) from perk_to_person where perkid not in (2, 3) order by persid

This gives me as result persids 1 and 2. But should give me 3. This is not possible at the moment because I'm not using the personid from table person.
0
 
Daniel WilsonCommented:
Select Person.*
From Person
Where Not exists (select PersID from Perk_to_Person Where PersID = Person.ID and Perk_To_Person.PerkID = 2)
0
 
TiTANS_CAuthor Commented:
Thanks for the answer!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now