Newbie simple SELECT/HAVING question 1

[leveetated]

Hello all,

I have a simple query that is just about to drive me nutty. I simply want to select the records in 1 table where the occurrence of a field value is limited to once.

This query does NOT work - and I don't know why. It returns all the rows where Ranking isn't only 0 (ranking = 0, ranking =1, ranking =2, etc.).

select id
from tbl1
inner join tbl2 on tbl2.objid=tbl1.id
where tbl1.ranking=0
and tbl1.tableid=108
and tbl2.classid=12
group by id
having count(*) = 1
order by id

The field value for id in this join should appear only once in tbl1, where ranking=0. I don't want records in tbl1 where the id (which is joined to tbl2.objid) value occurs more than once.

I hope I'm explaining this clearly, and I know this is a simple one. Any ideas, SQL experts?

Many thanks.

 

[bborissov]

That gives me correct result:

DECLARE @Tbl1 TABLE (Id int, Ranking int, TableId int)
DECLARE @Tbl2 TABLE (ObjId int, ClassId int)

INSERT INTO @Tbl1 VALUES (1, 0, 12)
INSERT INTO @Tbl1 VALUES (1, 1, 12)
INSERT INTO @Tbl1 VALUES (1, 0, 108)
INSERT INTO @Tbl1 VALUES (1, 1, 108)
INSERT INTO @Tbl1 VALUES (2, 0, 108)

INSERT INTO @Tbl2 VALUES (1, 12)
INSERT INTO @Tbl2 VALUES (1, 12)
INSERT INTO @Tbl2 VALUES (1, 12)
INSERT INTO @Tbl2 VALUES (1, 12)
INSERT INTO @Tbl2 VALUES (1, 12)
INSERT INTO @Tbl2 VALUES (2, 12)


select id
from @tbl1 tbl1
inner join @tbl2 tbl2 on tbl2.objid=tbl1.id
where tbl1.ranking=0
  and tbl1.tableid=108
  and tbl2.classid=12
group by id
having count(*) = 1
order by id

This is your query, nothing changed in it just table names


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[leveetated]

Thank you for your time on this. I tried the above and unfortunately it didn't solve my problem, but I was finally able to wrangle the correct query using a subquery (if anyone's interested):

-------------------
select id, objid from tbl1
join tbl2 on tbl2.objid=tbl1.id
where id in
(
select id
from tbl1
group by id
having (count(id) = 1)
)
and ranking=0
and tableid=108
and tbl2.classid=12
---------------------

Many thanks again!
Best to all