CODEWITHSUNDEEP
sqlpostgresql
JokerMartini
JokerMartini

Reputation: 6147

Passing value to subquery in PostgreSQL

I'm trying to collect all the families who live in states surrounding the user specified state by name. As you can see below. However I get this error when I execute the code and I'm not sure how to resolve it:

error:

ERROR:  syntax error at or near "x"
LINE 13:  families WHERE state_name IN x.names and family_name...

code:

with x as (
    SELECT 
        bordering_states_names as names
    FROM 
        states 
    WHERE 
        state_name = 'Ohio' 
)
SELECT 
    family_name,
    state_name
FROM 
    families WHERE state_name IN x.names and family_name IS NOT NULL
LIMIT 20

In this case the value of bordering_states_names looks like this:

{"Ohio", "Indiana", "West Virginia", "Pennsylvania"}

Upvotes: 1

Views: 3068

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65408

Table x should exist with a FROM part for the below query, otherwise x might have no-sense.

So, Joining those tables ( families and x ) might be an option as in the following case :

WITH x AS (
    SELECT 
        bordering_states_names as names
    FROM 
        states 
    WHERE 
        state_name = 'Ohio' 
)
SELECT f.family_name, f.state_name
  FROM families f inner join x
    ON ( f.state_name = x.names ) 
   AND family_name IS NOT NULL
 LIMIT 20;

Upvotes: 2

Lukasz Szozda
Lukasz Szozda

Reputation: 176189

You could rewrite it as:

with x as (
    SELECT bordering_states_names as names
    FROM states 
    WHERE state_name = 'Ohio' 
)
SELECT family_name,
       state_name
FROM families 
WHERE EXISTS (SELECT 1 FROM x WHERE state_name = ANY (names))
  AND family_name IS NOT NULL
LIMIT 20

Upvotes: 1

Related Questions