inventory=# \d+ SchemaVersions
Did not find any relation named "SchemaVersions".
inventory=# SELECT n.nspname as "Schema",
inventory-#   c.relname as "Name"
inventory-# FROM pg_catalog.pg_class c
inventory-#      LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
inventory-# WHERE  c.relname = 'SchemaVersions';
 Schema |      Name
--------+----------------
 public | SchemaVersions
(1 row)

inventory=#

inventory=# create database foo;
CREATE DATABASE
inventory=# \c foo
You are now connected to database "foo" as user "pvandivier".
foo=# create table SchemaVersions ( i int );
CREATE TABLE
foo=# select * from SchemaVersions;
 i
---
(0 rows)

foo=#

inventory=# create table SchemaVersions ( i int );
CREATE TABLE
inventory=# \d+
                                        List of relations
 Schema |                Name                 |   Type   |   Owner    |    Size    | Description
--------+-------------------------------------+----------+------------+------------+-------------
 public | SchemaVersions                      | table    | pvandivier | 8192 bytes |
 public | SchemaVersions_schemaversionsid_seq | sequence | pvandivier | 8192 bytes |

USE [StackExchange]
GO

/****** Object:  Table [dbo].[UpperCase]    Script Date: 10.12.2019 11:34:57 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[UpperCase](
	[anumber] [int] NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[UPPERCASE](
	[anumber] [int] NULL
) ON [PRIMARY]

GO

LocationID	LanguageID	Name
1	2	Bachblüten Essenzen
1	3	Bach Flowers Essences
1	4	Bach-virág
1	5	Fiori di Bach
2	2	Amazonas Mosquitospray (Gelsenspray)
2	3	Amazonas Mosquitospray
2	4	Amazonas szúnyogriasztó spray
2	5	Amazonas Mosquitospray

ID	Name	Description	DescriptionShort
2	2	645	641
3	3	644	640
4	4	655	642

SELECT *
FROM ProductsGroups pg
JOIN ProductsLocation locName ON pg.Name = locName.LocationID
JOIN ProductsLocation locDesc ON pg.Description = locDesc.LocationID

WITH productNames (Name, Description, DescriptionShort)
AS
(
SELECT locName.Name, Description, DescriptionShort
FROM ProductsGroups pg
JOIN ProductsLocation locName ON pg.Name = locName.LocationID
)
SELECT * FROM productNames pN
JOIN ProductsLocation locDesc ON locDesc.LocationID = pN.Description;

WITH productNames (Name, Description, DescriptionShort) AS
(
	SELECT locName.Name, Description, DescriptionShort
	FROM ProductsGroups pg
	JOIN ProductsLocation locName ON pg.Name = locName.LocationID
),
productNamesDesc AS
(
	SELECT p.Name, locDesc.Name as Description, p.DescriptionShort FROM productNames p
	JOIN ProductsLocation locDesc ON locDesc.LocationID = p.Description
	)
SELECT p.Name, p.Description, locShortDesc.Name FROM productNamesDesc p
JOIN ProductsLocation locShortDesc ON locShortDesc.LocationID = p.DescriptionShort