This is my query

SELECT org.id,
                org.name,
                org.type,
                org.company_logo,
                (SELECT org_profile.logo_url FROM org_profile WHERE org_profile.org_id = org.id AND org_profile.status = 'active' ORDER BY org_profile.id DESC LIMIT 1) as logo_url,
                org.short_description,
                org_profile.value_prop,
                count(*) OVER () AS total
                FROM org
                LEFT JOIN user_info ON user_info.id = org.approved_by
                INNER JOIN (select distinct org_profile.org_id from org_profile) org_profile ON org_profile.org_id = org.id
                WHERE
                org.type = 'Fintech'
                AND org.status = 'APPROVED'
                AND org.draft != TRUE
                ORDER BY org.id DESC
            

I am using LEFT JOIN query with my org_profile table. I used distinct for unique org id but the problem is org_profile.value_prop column does not work. The error is showing column org_profile.value_prop does not exist

I'm trying to solve this issue. But I don't figure out this issue.

CodePudding user response：

basically, the error informs that you try to get the value_prop field from org_profile subquery, which basically doesn't exist.

It's difficult to give a working query by writting just on the paper, but I think that:

• it's worth to apply the handy aliasses for each subquery
• deduplication, if required, should be done in the subquery. When multiple fields used DISTINCT may be insufficient - RANK function may be required.
• you make some operations to get the logo_url by a scalar subquery - it seems a bit strange, especially the same table is used in JOIN - I would suggest to move all logic related to org_profile to the subquery. Scalar expressions would throw an error in case multiple values would be found in output.

SELECT 
  org.id,
  org.name,
  org.type,
  org.company_logo,
  prof.logo_url,
  org.short_description,
  prof.value_prop,
  count(*) OVER () AS total
FROM org
JOIN (
  select distinct org_id, logo_url, value_prop -- other deduplication type (RANK) may be required
  from org_profile
  where status = 'active' -- ?
) prof ON org.id = prof.org_id
LEFT JOIN user_info usr ON usr.id = org.approved_by
WHERE
  org.type = 'Fintech'
  AND org.status = 'APPROVED'
  AND org.draft != TRUE
ORDER BY org.id DESC