From: "Bee.Lists" <bee.lists@gmail.com>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Subject: Outer Right Join?
Message-Id: <8BE08FFD-68C7-45D9-979D-8249AC502BD3@gmail.com>
Date: Thu, 20 Feb 2020 03:04:59 -0500
To: Submit Postgresql Novice <pgsql-novice@postgresql.org>
Mime-Version: 1.0 (Mac OS X Mail 9.3 \(3124\))
Precedence: bulk

I=E2=80=99m looking for a three-table join.  The SQL that I have isn=E2=80=
=99t working.

companies table - list of companies
products table - list of products sold by those companies
contracts table - list of companies that hold ad contracts

I need to ask:

"Show me the queried product (i.e.: Screwdriver) listings with their =
company names that DO NOT have contracts"

SELECT DISTINCT ON ("listings"."product")=20
  "listings"."product", "companies"."name"=20
  FROM "listings"=20
  RIGHT OUTER JOIN "contracts" ON ("contracts"."companyid" =3D =
"listings"."companyid")=20
  LEFT JOIN "companies" ON ("companies"."scid" =3D =
"listings"."companyid")=20
  WHERE (("listings"."product" ILIKE '%screwdriver%' ESCAPE '\'))=20

The result works without the RIGHT OUTER JOIN in there.  When the RIGHT =
OUTER JOIN is in there, I get a hitlist of zero.  Currently I have no =
contracts in that table, so those two queries should be the same.  They =
are not. =20

To repeat, I want any company=E2=80=99s products that are in the =
contracts table, to not show up.  =E2=80=9Cproducts with no contract=E2=80=
=9D. =20

Any inside as to how I can get this to work, appreciated.


Cheers, Bee