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

Answer 1

Use a join:

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

Answer 2

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