I am writing an SQL query on two tables
products02 in a PostgreSQL database.
For the entire set of records in
products02 take the substring (first 10 characters of
volumeId and use those substrings to find all the corresponding records in
products01. There can be more than one hit in
products01, for instance finding older legacy record of the same item, just a different edition. The eleventh character of
volumeId makes it unique.
For instance, in
products01 I can have
record45671 record45672 record45673
products02 I would be using
substring(volumeId for 10) or
'record456' to find those similar records in
Also I'd like to display the results so I can verify my query is accurate.
Here is my initial attempt:
SELECT substring(volumeId, for 10) FROM temporary.products01 t01 WHERE EXISTS ( SELECT volumeId FROM temporary.products02 t02 WHERE t01.volumeId LIKE t02.volumeId);