CODEWITHSUNDEEP
sqlpostgresqljoininner-joinstored-functions
Frank Wood
Frank Wood

Reputation: 59

Function to return a specific column from a table based on value

I have a query (PostgreSQL 15.4) like this:

Select p."prodId", p."prodDesc", p."areaName", x."location1" as "Location"
From ops."Products" p
Inner Join ops."Locations" x on p."prodId" = x."prodId" 
                             and p."areaName" = x."areaName1"

Here's the caveat: in the table Locations, there are three locations, location1, location2, and location3. In addition, there are three columns areaName1, areaName2, and areaName3.

I need to return the location column as either location1, location2, or location3, based on the column areaName in the Products table being equal to one of the corresponding areaName columns in Locations: areaName1, areaName2, or areaName3.

My thinking was to be able to use some sort of function where I could join the function to the rest of my query and have the function handle all of the above. In a land and time far away, I probably would've handled this as dynamic SQL, but wondering if there is a better way to go?

Upvotes: 0

Views: 82

Answers (2)

MatBailie
MatBailie

Reputation: 86775

You should change the locations table. Don't have the three (areaname, location) pairs in one row, have them in three rows.

You can do that inline in the query, but it will be slower (and less maintainable) than just fixing the data structure.

SELECT
  p."prodId",
  p."prodDesc",
  p."areaName",
  l."location"
FROM
  ops."Products"   p
INNER JOIN
(
  SELECT "prodId", 1 AS rowid, "areaName1" AS "areaName", location1 AS location FROM ops."Locations"
  UNION ALL
  SELECT "prodId", 2 AS rowid, "areaName2" AS "areaName", location2 AS location FROM ops."Locations"
  UNION ALL
  SELECT "prodId", 3 AS rowid, "areaName3" AS "areaName", location3 AS location FROM ops."Locations"
)
  AS l
    ON  p."prodId"   = l."prodId" 
    AND p."areaName" = l."areaName";

Upvotes: 0

Zegarek
Zegarek

Reputation: 26347

You can use a case to switch between them and an in/=any(array[]) to join on any one.
demo at db-fiddle

Select p."prodId"
     , p."prodDesc"
     , p."areaName"
     , case p."areaName" 
       when x."areaName1" then x."location1" 
       when x."areaName2" then x."location2" 
       when x."areaName3" then x."location3"
       end as "Location"
From ops."Products"  as p
Join ops."Locations" as x 
  on p."prodId"   =     x."prodId" 
 and p."areaName" in (  x."areaName1"
                      , x."areaName2"
                      , x."areaName3");
prodId prodDesc areaName Location
1 prodDesc1 areaName1_1 Location1_1
2 prodDesc2 areaName2 Location2

Upvotes: 0

Related Questions