public inbox for [email protected]  

help / color / mirror / Atom feed

From: Bee.Lists <[email protected]>
To: Submit Postgresql Novice <[email protected]>
Subject: Outer Right Join?
Date: Thu, 20 Feb 2020 03:04:59 -0500
Message-ID: <[email protected]> (raw)

I’m looking for a three-table join.  The SQL that I have isn’t 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") 
  "listings"."product", "companies"."name" 
  FROM "listings" 
  RIGHT OUTER JOIN "contracts" ON ("contracts"."companyid" = "listings"."companyid") 
  LEFT JOIN "companies" ON ("companies"."scid" = "listings"."companyid") 
  WHERE (("listings"."product" ILIKE '%screwdriver%' ESCAPE '\')) 

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.  

To repeat, I want any company’s products that are in the contracts table, to not show up.  “products with no contract”.  

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


Cheers, Bee

view thread (3+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected]
  Subject: Re: Outer Right Join?
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox