Member Avatar for moone009 
select PREMISEADDRESS1 from PREMISE PREM
inner join STATUS ST on PREM.LOCATIONID = ST.LOCATIONID
inner join SUPPLIES SUP on ST.STOPID = SUP.STOPID
where SUP.ITEMID = (select count(ITEMID) > 10) from SUPPLIES)

Hello,

I do not use SQL all that often but I would like to view all of the premisesaddresses that have more than 10 items attached to them. Can anyone help me out with this?

Thank you

  • 2 Contributors
  • 1 Reply
  • 78 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by buddylee17
Member Avatar for buddylee17

Use a GROUP BY with the HAVING clause. Haven't tested, but it should work.

select PREMISEADDRESS1 from PREMISE PREM
inner join STATUS ST on PREM.LOCATIONID = ST.LOCATIONID
inner join SUPPLIES SUP on ST.STOPID = SUP.STOPID
group by PREMISADDRESS1
HAVING COUNT(SUP.ITEMID) > 10

http://msdn.microsoft.com/en-us/library/ms180199.aspx

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.