CODEWITHSUNDEEP
sqlpostgresql
kafka
kafka

Reputation: 733

How can i have where clause in Nested queries with select statement that returns more than 1 records

I am currently trying to make an nested query of some sort where it returns a set of rows which fulfills a particular set of requirements..

select * from entity 
 where id = (
 select attribute_entity_id from attribute 
 where attribute_type_specification_id = (
 select id from attribute_type_specification  
 where entity_internal_name = 'country'
 )
 ) 
 order by entity_id

Though this returns error:

ERROR: more than one row returned by a subquery used as an expression

Which is true, but how i should make this call then? The inner select might return x number of id's which are valid?

Upvotes: 0

Views: 567

Answers (1)

Mike Organek
Mike Organek

Reputation: 12494

Is there a reason why you are using subselects to accomplish this?

The regular way to do this is with joins:

select e.*, ats.entity_internal_name
  from entity e
  join attribute a
    on a.attribute_entity_id = e.id
  join attribute_type_specification ats
    on ats.id = a.attribute_type_specification_id
 where ats.entity_internal_name = 'country'
 order by e.entity_id 
;

Upvotes: 1

Related Questions