Can't figure out how to build an sqlquery with negation.
Posted on 2008-10-07
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.
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)
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?