Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2013
    Posts
    2

    Unanswered: Search for missing values in SQL. Advanced Query

    KEY ID GROUP
    1 1 a
    2 1 b
    3 2 a
    4 2 b
    5 3 a
    6 3 b
    7 4 a
    8 5 a

    This is my simple table I need a query that will identity the ID's that are missing the group "b" but I don't want ID 1,2,3 to come up because they are part of a and b. I just need to see anything missing only "b" but not if it's part of a and b.

    query should reveal answer should be missing the group b
    KEY ID
    7 4
    8 5

    I tried the NULL search but since the records dont exist it cant find a null. Any help on writing a query to identify the missing ID without B but exclude ID that are part of A and B

    Hope this makes sense, Please help.

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Try:

    Code:
    select Key, Id from MyTable as a
    where a.Group = 'a' and
          not exists (select 1 from MyTable as b
                      where b.Group = 'b' and 
                      b.Id = a.Id)
    Hope this help.

  3. #3
    Join Date
    Jan 2013
    Posts
    2

    IT worked!!!!!!!

    select * from dbo.Groups as GRP1
    join dbo.Vcomputer on GRP1.[id] = dbo.Vcomputer.[ID]
    join [dbo].[Buildver]on GRP1.[id] = [dbo].[Buildver].[ID]
    where [Group] = 'a' and
    not exists (select 1 from dbo.Groups as GRP2
    where [Group] = 'b' and
    GRP2.Id = GRP1.Id)


    THANK YOU THANK YOU THANK YOU THANK YOU!! SO SO SO SO MUCH
    I spent 2 days trying to figure this out.. Thanks again!!

  4. #4
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Please learn to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

    KEY is a reserved word, GROUP is a reserved word and ID is what ISO-11179 calls a table property so it cannot be a data element name. Why do you need ID at all? It looks like a fake record number that non-SQL programmers build with IDENTITY, but that cannot be right.

    Her is my guess at correct DDL:

    CREATE TABLE Foobar
    (foo_id INTEGER NOT NULL,
    foo_version CHAR(1) NOT NULL
    CHECK (foo_version IN ('a', 'b')),
    PRIMARY KEY (foo_id, foo_version));

    INSERT INTO Foobar
    VALUES (1, 'a'), (1, 'b'),
    (2, 'a'), (2, 'b'),
    (3, 'a'), (3, 'b'),
    (4, 'a'),
    (5, 'a');

    The query is now easy:

    SELECT foo_id
    FROM Foobar
    GROUP BY foo_id
    HAVING MIN(foo_version) = MAX(foo_version)
    AND MIN(foo_version) = 'b';

Posting Permissions

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