Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Location
    New York, NY
    Posts
    225

    Thumbs up Unanswered: Resolved - SQL String Question

    It is early in the morning and I can't seem to create a SQL String that will select the records that I need. I have a table that has three fields (Serial_ID, CaseNum, IType). Now it is possible to have different type associated with a Case Number but I want to be able to select the Case Numbers that only have one type associated with them.

    I have attached a sample of the data contained in the table.

    I want Case numbers D06-0002, D06-0003 and D06-0005 returned.

    So Far I have Tried this:

    Code:
    SELECT TAB1.CASENUM, TAB1.IType, COUNT(TAB1.IType)
    FROM TABLE1 as TAB
    GROUP BY TAB1.CASENUM, TAB1.IType
    HAVING COUNT(TAB1.IType) > 1
    ORDER BY TAB1.CASENUM
    Basically this SQL string brings back the entire dataset.

    Any ideas?
    Attached Thumbnails Attached Thumbnails Table1-1.JPG  
    Last edited by Mark Gambo; 10-31-06 at 09:10.

  2. #2
    Join Date
    May 2003
    Location
    New York, NY
    Posts
    225
    I had to use the Count Distinct Function, I found it by reading this LINK

    Code:
    SELECT TAB1.CASENUM, TAB1.IType, COUNT(DISTINCT TAB1.IType)
    FROM TABLE1 as TAB
    GROUP BY TAB1.CASENUM, TAB1.IType
    HAVING COUNT(TAB1.IType) = 1
    ORDER BY TAB1.CASENUM
    It works like a charm.

  3. #3
    Join Date
    Oct 2004
    Location
    New Jersey
    Posts
    65
    It also could have been because you have some kind of a self-join going on. Your fields are prefixed "TAB1" and you are selecting "FROM TABLE1 as TAB"
    Retired Moderator at Xtreme VB Talk, for all of your Visual Basic needs.

Posting Permissions

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