David Tunnell David Tunnell - 1 year ago 38
SQL Question

Filtering by number of rows a primary key has

I have a query which sometime returns rows that are not needed.

SELECT TOP 100 PERCENT ca.item_id
,ca.FIELD_ID
,ca.attr_val
,ca.upd_dtt
,ca.upd_usr
FROM contract_attr ca
WHERE EXISTS (
SELECT 1
FROM contract_attr ca_326
WHERE ca.item_id = ca_326.item_id
AND ca_326.field_id = 326
AND ca_326.ATTR_VAL = 'Y'
)
UNION ALL
SELECT ca.item_id
,9999
,mf.[ITEM_NAME]
,''
,''
FROM mfr mf
JOIN contract_attr ca ON ca.attr_val = mf.[ITEM_PK]
ORDER BY ca.item_id


enter image description here

item_id's 10-13 on the image only have 1 row.

I want to filter these rows from the query. Looking it up I'm supposed to add a having:

SELECT TOP 100 PERCENT ca.item_id
,ca.FIELD_ID
,ca.attr_val
,ca.upd_dtt
,ca.upd_usr
FROM contract_attr ca
WHERE EXISTS (
SELECT 1
FROM contract_attr ca_326
WHERE ca.item_id = ca_326.item_id
AND ca_326.field_id = 326
AND ca_326.ATTR_VAL = 'Y'
)
UNION ALL
SELECT ca.item_id
,9999
,mf.[ITEM_NAME]
,''
,''
FROM mfr mf
JOIN contract_attr ca ON ca.attr_val = mf.[ITEM_PK]
HAVING count(ca.item_id) > 1
ORDER BY ca.item_id


But I am getting this error and don't understand why:
Column 'contract_attr.ITEM_ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


What am I doing wrong and how do I fix it?

Answer Source

Since you are wanting to return multiple columns which wouldn't be able to be done with the grouping required by having, you can use having in a sub query.

SELECT TOP 100 PERCENT 
    ca.item_id
    ,ca.FIELD_ID
    ,ca.attr_val
    ,ca.upd_dtt
    ,ca.upd_usr
FROM contract_attr ca
            WHERE EXISTS (
        SELECT 1
        FROM contract_attr ca_326
        WHERE ca.item_id = ca_326.item_id
            AND ca_326.field_id = 326
            AND ca_326.ATTR_VAL = 'Y'
        )
UNION ALL
SELECT ca.item_id
    ,9999
    ,mf.[ITEM_NAME]
    ,''
    ,''
FROM mfr mf
JOIN contract_attr ca ON ca.attr_val = mf.[ITEM_PK]
INNER JOIN(SELECT item_id FROM contract_attr group by item_id having count(item_id) > 1) x on x.item_id = ca.item_id
ORDER BY ca.item_id
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download