Query assistance to show unique row

• osxman

  Old Hand

  Points: 366

  More actions

  August 15, 2018 at 2:40 am

  #376108

  I have produced the query below to list all staff who have a udf value equal to 25.

   SELECT Staff.Person_id, Udf.value as Value, Staff.Forename, Staff.Midname, Staff.Surname,udf.field_id,
                                    Staff.employment_end
                             FROM  Staff_Report AS Staff
                                 
                            left join udf_value AS Udf ON Staff.Person_id = Udf.entity_id
           WHERE (Udf.field_id = '25') and (Staff.employment_end IS NULL)
   order by Staff.surname asc

  
  This works fine. However, I would now like to show staff (one record for each staff) who don't have a value equal to 25. The problem is that a person can have many udf values.

  for example:

  person_id, Forename, Surname, Field_Id
  1 ,             Joe ,            Blogs,      25
  1 ,             Joe ,            Blogs,      88
  1 ,             Joe,             Blogs,      19
  2 ,             Jane,           Doe,         88
  2 ,             Jane,           Doe,         23
  2,              Jane,           Doe,         66

  2,              Jane,            Doe,        119

  From the above data I would want to see 1 row of data (doesnt matter which field_id value is displayed. All I am interested in is the person id, forename, surname, etc

  2             Jane            Doe        88

  I  wouldn't want to see Joe as he has a Field_id equal to 25. Jane does not have a value equal to 25 so one record should be displayed.

  Any assistance would be much appreciated.

  Cheers,
  John

• laurie-789651

  SSCertifiable

  Points: 7680

  More actions

  August 15, 2018 at 4:05 am

  #2001801

  Left join to Udf table filtered on 25 only, & only show staff with no join:

  
SELECT Staff.Person_id, Udf.value as Value, Staff.Forename, Staff.Midname, Staff.Surname,udf.field_id,
Staff.employment_end
FROM Staff_Report AS Staff
left join udf_value AS Udf ON Staff.Person_id = Udf.entity_id AND Udf.Field_id = 25
WHERE (Staff.employment_end IS NULL) AND Udf.entity_id IS NULL
order by Staff.surname asc;


• osxman

  Old Hand

  Points: 366

  More actions

  August 15, 2018 at 4:43 am

  #2001806

  Perfect solution.
  Thanks,

  John

• Jonathan AC Roberts

  SSCoach

  Points: 18347

  More actions

  August 15, 2018 at 4:51 am

  #2001808

  ;WITH CTE AS
(
  SELECT DISTINCT
    Staff.Person_id,
    Staff.Forename,
    Staff.Midname,
    Staff.Surname
  FROM Staff_Report AS Staff
  WHERE NOT EXISTS(SELECT *
        FROM udf_value AS Udf
        WHERE Udf.Person_id=Staff.Person_id
         AND Udf.value = 25 )
)
SELECT *
FROM CTE
OUTER APPLY(SELECT TOP(1)
       udf.field_id AS Udf_field_id
     FROM udf_value AS Udf
     WHERE Udf.Person_id=CTE.Person_id
     ORDER BY <whatever you want> ) AS Udf
ORDER BY CTE.surname ASC

• osxman

  Old Hand

  Points: 366

  More actions

  September 28, 2018 at 11:57 pm

  #2007418

  Jonathan AC Roberts - Wednesday, August 15, 2018 4:51 AM

  ;WITH CTE AS
(
  SELECT DISTINCT
    Staff.Person_id,
    Staff.Forename,
    Staff.Midname,
    Staff.Surname
  FROM Staff_Report AS Staff
  WHERE NOT EXISTS(SELECT *
        FROM udf_value AS Udf
        WHERE Udf.Person_id=Staff.Person_id
         AND Udf.value = 25 )
)
SELECT *
FROM CTE
OUTER APPLY(SELECT TOP(1)
       udf.field_id AS Udf_field_id
     FROM udf_value AS Udf
     WHERE Udf.Person_id=CTE.Person_id
     ORDER BY <whatever you want> ) AS Udf
ORDER BY CTE.surname ASC

  Great, Thanks!

• osxman

  Old Hand

  Points: 366

  More actions

  October 4, 2018 at 3:49 am

  #2008011

  Jonathan AC Roberts - Wednesday, August 15, 2018 4:51 AM

  ;WITH CTE AS
(
  SELECT DISTINCT
    Staff.Person_id,
    Staff.Forename,
    Staff.Midname,
    Staff.Surname
  FROM Staff_Report AS Staff
  WHERE NOT EXISTS(SELECT *
        FROM udf_value AS Udf
        WHERE Udf.Person_id=Staff.Person_id
         AND Udf.value = 25 )
)
SELECT *
FROM CTE
OUTER APPLY(SELECT TOP(1)
       udf.field_id AS Udf_field_id
     FROM udf_value AS Udf
     WHERE Udf.Person_id=CTE.Person_id
     ORDER BY <whatever you want> ) AS Udf
ORDER BY CTE.surname ASC

  Thanks!