Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2005
    Posts
    165

    Unanswered: Problem constructing query to weed out some results...

    Hi! I'm trying to put together some reports for an attorney's office. I'm having trouble constructing this query. What I'm doing is querying for defendants who do not have a particular charge against them.

    Here are my tables:
    Code:
    DCase
    -------------------------------
    VBKey      CaseNumber
    1          33365
    2          66585
    etc..
    Code:
    DCharge
    -------------------------------
    VBKey      ChargeNum
    1          27
    2          19
    3          20
    3          21
    etc..
    Since both tables are linked via VBKey, I joined them together and this is where I got stuck.

    I tried using HAVING ChargeNum<>21 but it would still return a result b/c VBkey=3 has two charges against him. So, when do my queries, I'm still getting individuals who have multiple charges. I tried doing this with SELECT DISTINCT but it would still give me results from people with 3 or more charges. I'm lost at how to complete this query. Any help will be greatly appreciated! Thank you!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT *
       FROM DCase
       WHERE NOT EXISTS (SELECT *
          FROM DCharge
          WHERE DCharge.VBKey = DCase.VBKey
             AND 21 = ChargeNum)
    -PatP

  3. #3
    Join Date
    Jan 2005
    Posts
    165
    Thank you very much Pat! That worked perfectly! Do you know of any good books I can use to better my sql skills?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There are quite literally thousands of good books on using SQL. There are lots of tips and tricks that are product specific, and those are especially important for someone who's getting started.

    If your primary environment is VB, I'd recommend Hitchhiker's Guide to Visual Basic and SQL Server. If you're looking for something a bit more generic, I'd suggest SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL. If you really want to "grok" SQL, then I'd recommend almost anything by Joe Celko, but especially Joe Celko's SQL for Smarties: Advanced SQL Programming. In the interest of "full disclosure", while I count all of these folks as my friends or acquaintances, I make nothing from their books.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •