sql multiple subqueries in WHERE clause

Question

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.

Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSI JOIN syntax in the ANSI-92 SQL Standard (more than 20 years ago) and its use is discouraged — marc_s
– marc_s, Commented Jul 17, 2016 at 17:12
Which RDBMS is this for? Please add a tag to specify whether you're using mysql, postgresql, sql-server, oracle or db2 - or something else entirely. — marc_s
– marc_s, Commented Jul 17, 2016 at 17:12

Gordon Linoff · Accepted Answer · 2016-07-17 15:29:39Z

1

The idea behind the query is fine. But you don't need two tables in the from clause; you want correlated subqueries:

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

answered Jul 17, 2016 at 15:29

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jerome Bravin · Accepted Answer · 2016-07-17 18:00:45Z

0

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')

answered Jul 17, 2016 at 18:00

Jerome Bravin

6610 bronze badges

1 Comment

Alex Kudryashev Over a year ago

What if rows are 001 | Lot | Avondale Spring || 001 | SubDiv | 546? @GordonLinoff's answer is better.

Collectives™ on Stack Overflow

sql multiple subqueries in WHERE clause

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related