CODEWITHSUNDEEP
sqlsql-server
sharptooth
sharptooth

Reputation: 170519

How do I select items from one table such that a query in another table yields exactly one row?

I have two tables: Jobs and JobItems:

CREATE TABLE Jobs (
    [JobId] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY
);

Create TABLE JobItems(
    [ItemId] UNIQUEIDENTIFIER NOT NULL,
    [JobId] UNIQUEIDENTIFIER NOT NULL
);

and for every row in Jobs there can be zero, one, or more rows in JobItems such that their JobId matches that of the row in Jobs which means that zero, one or more than one "job" contains that "item".

I need to select rows from Jobs such that there's exactly one row in JobItems with JobId matching that of the row from Jobs and tried code from this answer (altered a bit):

SELECT JobId, (SELECT Count(ItemId) from JobItems WHERE
     JobItems.JobId=Jobs.JobId) from Jobs

and the problem is I now need to somehow say that I need only rows with exactly one match. So I tried to assign a shortcut to Count(ItemId)

SELECT JobId, (SELECT Count(ItemId) AS CountItemId from JobItems WHERE
     JobItems.JobId=Jobs.JobId) from Jobs WHERE CountItemId=1

but this makes SQL server unhappy - it says

Invalid column name CountItemId

How do I have only items with exactly one match selected?

Upvotes: 1

Views: 89

Answers (5)

dbetke
dbetke

Reputation: 28

You can also create a temporary result set: 

WITH T1 AS (
  SELECT JobId
  FROM Jobs 
),
T2 AS (
  SELECT JobId, Count(ItemId) AS CountItemId 
  FROM JobItems 
  GROUP BY JobID
)

SELECT JobId, CountItemId
FROM T2
WHERE CountItemId = 1

Upvotes: 1

Madara Uchiha
Madara Uchiha

Reputation: 126

Select jobid
From jobitems 
Group by jobid
Having count(*) = 1;

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 95072

You can do this:

select *
from jobs i
where (select count(*) from jobitems ji where ji.jobid = j.jobid) = 1;

If however, table Jobs only contains all job IDs and nothing else, then you don't have to select from table Jobs at all:

select jobid
from jobitems 
group by jobid
having count(*) = 1;

Upvotes: 1

Code Lღver
Code Lღver

Reputation: 15593

Use this query:

SELECT JobId, Count(ItemId) from JobItems group by JobId;

Upvotes: 1

Mudassir Hasan
Mudassir Hasan

Reputation: 28771

A basic GROUP BY HAVING clause will do it for you..

      SELECT Jobs.jobId
      FROM Jobs 
      INNER JOIN JobItems
         ON Jobs.JobId = JobItems.JobId
      GROUP BY Jobs.jobId
      HAVING COUNT(*)=1

Upvotes: 2

Related Questions