postgresql - declare value as having an alias that essentially makes it the same - Stack Overflow

admin管理员组

文章数量:1312817

kind of an odd question, is there a way to lookup a list of values and for the ones that don't exist use an alias and do a lookup on that instead.

matching codes/alias table (say this table doesn't exist and I don't want it to I would like to declare the matches at the start of my query and have it search either or, so say if it's itemcode abcd if I declare it at the top and say it could also be called 1200 look for either abcd or 1200 as they mean the same itemcode essentially)

itemcode	alternatecode
abcd	1200
xxxx	1800
bark	2000
home	2100
sled	5000
tech	7500
bell	6350

kind of an odd question, is there a way to lookup a list of values and for the ones that don't exist use an alias and do a lookup on that instead.

matching codes/alias table (say this table doesn't exist and I don't want it to I would like to declare the matches at the start of my query and have it search either or, so say if it's itemcode abcd if I declare it at the top and say it could also be called 1200 look for either abcd or 1200 as they mean the same itemcode essentially)

itemcode	alternatecode
abcd	1200
xxxx	1800
bark	2000
home	2100
sled	5000
tech	7500
bell	6350

so say if this is the data below

itemcode	price
abcd	9.75
xxxx	10.00
bark	11.05
sled	17.12
tech	21.00
2100	15.50
6350	12.75

if I have a query where I only refer to the itemcode in the main column so say like

   SELECT itemcode, price 
   FROM pricing
   WHERE itemcode in ('abcd','xxxx','bark','home','sled','tech','bell')

I would need the outcome to look for the alias and return the main code with the price, outcome below

itemcode	price
abcd	9.75
xxxx	10.00
bark	11.05
sled	17.12
tech	21.00
home	15.50
bell	12.75

is there a way to declare matching ones before hand at the top?

thanks!

• postgresql
• alias
• declaration
• matching

Share Improve this question Follow asked Jan 31 at 19:13 SiegeSiege 911 silver badge55 bronze badges

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 0

You could select out the itemcodes that match and then make a union of the itemcode and alternatecode to drive your query.

Something like:

with a AS 
(
  SELECT * 
  FROM alias 
  WHERE itemcode in ('abcd','xxxx','bark','home','sled','tech','bell')
)
, aliases AS --union both columns
(
  SELECT itemcode
  FROM a
  UNION ALL
  SELECT CAST(alternatecode AS VARCHAR(10))
  FROM a
)
SELECT *
FROM pricing
WHERE itemcode in (SELECT itemcode from aliases)

dbfiddle here

You can JOIN alternatecode's, if necessary.

See example

select coalesce(c.itemcode,p.itemcode) itemcode, p.price
from prices p
left join codes c on p.itemcode=c.alternatecode

itemcode	price
abcd	9.75
xxxx	10
bark	11.05
sled	17.12
tech	21
home	15.5
bell	12.75

All columns of JOIN is

select *
from prices p
left join codes c on p.itemcode=c.alternatecode

itemcode	price	itemcode	alternatecode
abcd	9.75	null	null
xxxx	10	null	null
bark	11.05	null	null
sled	17.12	null	null
tech	21	null	null
2100	15.5	home	2100
6350	12.75	bell	6350

fiddle

本文标签： postgresqldeclare value as having an alias that essentially makes it the sameStack Overflow