How do I query these tables. Maybe use CASE expression?

In-efficient 61 Reputation points

I have two tables: dbo.GeneralInfo(ID, FYQtr) and dbo.EmpData(ID, Status, FYQtr). Both these tables are joined on two fields: ID & FYQtr.
My question is if dbo.GeneralInfo.ID has more than one entry in the dbo.EmpData.Status column, I want it to say "Multiple Entry", which ID 1001 & 1002 have multiple entry in Status field. How do I achieve this?


Here is the query:SELECT DISTINCT A.*, B.Status FROM TestEmpID..GeneralInfo A JOIN TestEmpID..EmpData B ON A.ID=B.ID AND A.FYQtr=B.FYQtr

Here are the table definitions and their data:
CREATE TABLE [TestEmpID]..EmpData;
INSERT INTO TestEmpID..EmpData(ID,[Status], FYQtr)

CREATE TABLE TestEmpId.dbo.GeneralInfo([ID] INT,[FYQtr] VARCHAR(10));
INSERT INTO CREATE TABLE [TestEmpID].[dbo].GeneralInfo

No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 68,621 Reputation points MVP

    Could this be something?

    SELECT A.ID, A.FYQtr,B.Status,
           CASE WHEN MIN(B.Status) OVER (PARTITION BY A.ID) <>
                     MAX(B.Status) OVER (PARTITION BY A.ID)
                THEN 'Multiple values'
    FROM   GeneralInfo A
    JOIN   EmpData B ON A.ID = B.ID
                   AND A.FYQtr = B.FYQtr

0 additional answers

Sort by: Most helpful