"asset" table contains 163 rows
"asset_attr_value" table contains 200 rows (but only 19 rows match
where clause below)

This SQL using EXISTS returns 163 rows -

SELECT asset_id
FROM asset
WHERE
EXISTS (SELECT asset_id
FROM asset_attr_value av
WHERE ( av.clasfn_schm_key = 'base'
AND av.attr_key =
'test_attr_base_1'
AND av.value_text = 'a1' )
AND av.record_deleted = 'N')
ORDER BY asset_id


But this one using IN returns 19 -

SELECT asset_id
FROM asset
WHERE
asset_id IN (SELECT asset_id
FROM asset_attr_value av
WHERE ( av.clasfn_schm_key = 'base'
AND av.attr_key =
'test_attr_base_1'
AND av.value_text = 'a1' )
AND av.record_deleted = 'N')
ORDER BY asset_id

The IN is correct but why does the 1st return all, surely this should
return 19 also?

thanks in advance

On Aug 12, 11:19*am, "Preston" <dontwant... (AT) nowhere (DOT) invalid> wrote:
ah ha!

SELECT a.asset_id
FROM asset a
WHERE
EXISTS (SELECT av.asset_id
FROM asset_attr_value av
WHERE ( av.clasfn_schm_key = 'base'
AND av.attr_key =
'test_attr_base_1'
AND av.value_text = 'a1' )
AND av.record_deleted = 'N' and
a.asset_id = av.asset_id)
ORDER BY a.asset_id

thanks for that, is there any advantage is using IN over EXISTS in
this case?