I have a table that looks like this
fID_a fID_b
1 1
1 2
2 2
3 1
I want all fID_a where fID_b is 1 AND where fID_a is a single record in that table.
I have a sql query that looks like this
select fID_a from tbl
where fID_b = 1
group by fID_a
having count(*) = 1
But that query still includes the fID_a 1 even though there are 2 records in that table!
Try this
SELECT fID_a FROM tbl
GROUP BY fID_a
HAVING MAX(fID_b)=1
AND MIN(fID_b)=1
(fid_a, fid_b) index. (@Markus : add this index!)
If you use a where clause then you would filter out other values what would make the count inaccurate.
select fID_a
from tbl
group by fID_a
having count(*) = 1
and sum(case when fID_b = 1 then 1 else 0 end) = 1
case there.
having count(*) = 1 and sum(fID_b) = 1 would be enough. Is there anything in MSSQL that would make it fail?fID_b being 0?You should try-self join to achieve this:
SELECT t1.fID_a
FROM tbl t1
JOIN tbl t2
ON t1.fid_a = t2.fid_a
AND t1.fid_b = 1
GROUP BY t1.fID_a
HAVING COUNT(*) = 1
WHERE fid_b = 1. Because if we use WHERE fid_b = 1 directly, it will take fid_a = 1 and fid_a = 6 as well. By using JOIN we can get exact count with condition WHERE fid_b = 1.select fID_a,fID_b
from tb1, (select fID_a
from tb1
group by fID_a
having count(*) = 1) temp
where tb1.fID_a = temp.fID_a
and fID_b = 1;
This should work. Your query does not work beacuse mysql computes where clause before having clause.
select fID_a from
(select fID_a, fID_b
from tbl
group by fID_a
having count(*) = 1) temp
where fID_b = 1
