Gordon Slater Gordon Slater - 4 months ago 18
SQL Question

sql multiple subqueries in WHERE clause

I have the following tables:

address_components (table)

ADDRESS_ID STREET_NUMBER STREET_NAME
-----------------------------------------------------
001 1402 Motz
002 505 31st
003 4100 Stimmel


attributes (table)

ID ATTRIBUTE_DEFINITION_ID VALUE
----------------------------------------------------------------
001 Lot 546
001 SubDiv Avondale Spring
002 Lot 546
002 SubDiv Garden
003 Lot 131
003 SubDiv Avondale Spring


And the following SQL statement:

SELECT
street_number,street_name
FROM
address_components a, attributes attr
WHERE
a.address_id = attr.id
AND EXISTS (SELECT 1 FROM attributes
WHERE attr.attribute_definition_id = 'Lot'
AND attr.value = '546')
AND EXISTS (SELECT 1 FROM attributes
WHERE attr.attribute_definition_id = 'SubDiv'
AND attr.value = 'Avondale spring')


I am trying to select address_components row "001" because I am trying to retrieve rows that have both attribute entries of "lot = 546" and SubDiv = Avondale Spring". Not either but both attributes must exist.

The query is returning no matches; however, when I query using a single subquery (eliminate 1 of the 2 subqueries) the requested rows are returned for that single query.

Answer
SELECT top 1 a.street_number,a.street_name from address_components a
              inner join attributes k on  a.ADDRESS_ID=k.ID
              WHERE k.ATTRIBUTE_DEFINITION_ID in('Lot','SubDiv')  AND
              k.value in('546','Avondale Spring')
Comments