ERROR: more than one row returned by a subquery used as an expression. POSTGRESQL / LOOKER

Question

I am trying to do a subquery in Looker that returns the assignee name from the person table where the person ID equals the assignedtoperson ID

I have tried to do a limit 1, but that just returns the first value for person in every row

SELECT 
    "ID",

    (SELECT 
        p."NAME"
    FROM 
        view_321782990.person AS p, view_321782990.request AS r 
    WHERE 
        p."ID" = r."ASSIGNEDTOPERSON") 
FROM view_321782990.request

I wanti it to return the Name of who was assigned to the ticket, doing

SELECT 
        p."NAME", r."ID"
    FROM 
        view_321782990.person AS p, view_321782990.request AS r 
    WHERE 
        p."ID" = r."ASSIGNEDTOPERSON"

returns what I want, however, I need to do the subquery this way because of how looker interprets SQL

Sounds like you need to use a join rather than a correlated subquery. — HSchmale, May 29 '19 at 20:01
I have to use a subquery due to the way looker formats the sql statements — Andrew, May 29 '19 at 20:18
What is the LookML situation that you're trying to create that's causing this? Explaining the root goal you're trying to solve would be helpful, because it's possible there's a better way to model it out! — Izzy Miller, May 30 '19 at 01:53

score 0 · Answer 1 · answered May 29 '19 at 20:34

0

Use a join:

SELECT
  p.name
  , r.id
FROM view_321782990.person p
LEFT JOIN view_321782990.request r ON
  p.id = r.assignedtoperson

answered May 29 '19 at 20:34

gr1zzly be4r

2,072
1
18
33

I would, however I need to use a subquery due to how looker creates the query. I need to have SELECT "ID" ... FROM view_321782990.request, and can not change anything but what is between SELECT "ID" and FROM – Andrew May 29 '19 at 20:38
With the information that you gave in your question you do not need a subquery. – gr1zzly be4r May 29 '19 at 20:40
I know that it can be done (and should be done) with a join, but I am wondering if it can be done with a subquery, because that is the way Looker is formatting the query for a look – Andrew May 29 '19 at 20:42

Will · Answer 2 · 2019-06-06T04:42:41.967

I think you may be able to get what you want with an inner query that's filtered by a value in the outer query and putting the LIMIT back in:

SELECT 
    "ID",

    (SELECT 
        p."NAME"
    FROM 
        view_321782990.person AS p, view_321782990.request AS r 
    WHERE 
        p."ID" = r."ASSIGNEDTOPERSON" AND r."ID" = outer_r."ID"
    LIMIT 1
) 
FROM view_321782990.request AS outer_r

Where request."ID" is a way of distinguishing from record with p."ID" = r."ASSIGNEDTOPERSON" This method depends on the relationship between these tables and may not be feasible in all situations, which brings me to:

I agree with the general consensus that this could be more easily done in the join. You may need to add a join in the explore if it's not there, but once there's a join between the views, you can access fields across views like so:

view: request {
  # ... sql_table_name, etc ...
  dimension: person_name {
    sql: ${person.name}
  }
  # ... mode dimensions, measures, etc ...
}

If you've got a clear idea of what the generated sql will need to look like, I'd take a look here for more on how looker generates sql, and here for more on how joins are handled

ERROR: more than one row returned by a subquery used as an expression. POSTGRESQL / LOOKER

2 Answers2