Post reply

How can I get a count of records from one table that have no related record in another?

  • January 4, 2012 at 12:52 pm

    #249131

    I am a beginner (obviously) and I want to get a count of roles from table incident that have not been assigned (no related record in table RoleAssignment). I've tried

    SELECT COUNT(*)

    FROM IncidentRoles

    WHERE IncidentID=@IncidentID

    EXCEPT

    SELECT RoleAssignment.IncidentRoleID

    FROM RoleAssignment

    WHERE TicketID=@TicketID

    but it's not returning the correct count. Thanks in advance for any help, I'm not sure what to do. I could just to a count from each table and compare them, but in the event a role is assigned twice it wouldn't be accurate.

  • January 4, 2012 at 12:59 pm

    #1428614

    i think you want to use the NOT IN operator, right?

    SELECT COUNT(*)

    FROM IncidentRoles

    WHERE IncidentID NOT IN(

    SELECT RoleAssignment.IncidentRoleID

    FROM RoleAssignment

    WHERE IncidentRoleID IS NOT NULL)

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 4, 2012 at 1:05 pm

    #1428621

    Awesome - ten million thank you's.

  • January 4, 2012 at 1:16 pm

    #1428627

    Err, never mind. Lowell beat me to it. My solution doesn't take into consideration duplicate IncidentID's. Not sure if they will repeat or not.

    Is this what you're looking for?

    if exists (select * from sys.objects where object_id = object_id(N'RoleAssignment') and type = N'u')

    drop table RoleAssignment

    if exists (select * from sys.objects where object_id = object_id (N'IncidentRoles') and type = N'u')

    drop table IncidentRoles

    --Create test tables

    create table IncidentRoles(IncidentID int, RoleID int)

    create table RoleAssignment(RoleID int)

    --insert sample data

    insert into IncidentRoles(IncidentID,RoleID)

    select 1,1

    union all

    select 1,2

    union all

    select 2,3

    union all

    select 3,4

    union all

    select 4,5

    insert into RoleAssignment

    select 1

    union all

    select 2

    --This is the query I believe you're looking for

    select COUNT(ir.RoleID)

    from IncidentRoles ir

    left outer join RoleAssignment ra on ir.RoleID = ra.RoleID

    where ra.RoleID is null

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply