Обсуждение: Outer Right Join?
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
First problem is the database design is no good.
1. If companies <—> products is many-to-many, you need a cross-reference table between them.
2. If companies <—> contracts is one-to-one, optional, then contracts should be a list of contracts (not of companies), or, if there are no significant attributes of a contract, merged into companies as a status flag or boolean (is there a contract with this company or not?).
It is almost never a good idea to have the same entity (in this case, companies) stored in more than one table solely because of varying attribute or relationship values.
It’s not a good idea to try to twist SQL into compensating for a confused database design.
Note: There is no way that SELECT DISTINCT needs to be part of your solution.
You might want to have a look at a book such as this one:
Cheers,
Mark
On Feb 20, 2020, at 03:04, Bee.Lists <bee.lists@gmail.com> wrote: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
On Thu, Feb 20, 2020 at 1:05 AM Bee.Lists <bee.lists@gmail.com> wrote:
"Show me the queried product (i.e.: Screwdriver) listings with their company names that DO NOT have contracts"
SELECT DISTINCT ON ("listings"."product")
You should almost never need to use DISTINCT, and even the "ON" variant is very specialized. Mark's comments about your data model come into play here - I do not know or care to dig into whether this specific situation warrants the DISTINCT, but it problem does not if you don't go and add unnecessary tables to the FROM clause.
"listings"."product", "companies"."name"
FROM "listings"
RIGHT OUTER JOIN "contracts" ON ("contracts"."companyid" = "listings"."companyid")
Your comments suggest you do not understand what the word "RIGHT" is communicating here (the word OUTER is just noise, you can omit it like you did for LEFT JOIN below)
listing RIGHT JOIN contracts -- list every contract in the system, and if there is related listing information show that as well. You said there are no contracts so it should not be surprising that this join returns zero records.
LEFT JOIN "companies" ON ("companies"."scid" = "listings"."companyid")
Actually, I can never remember whether sequential joins bind left-to-right or right-to-left but "contracts LEFT JOIN companies" is likewise an empty set so you get the same result.
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”.
You want records where corresponding records in the contracts table do "not exist". There is an SQL expression, written "NOT EXISTS (subquery)" that does exactly this.
SELECT *
FROM company
WHERE NOT EXISTS (SELECT 1 FROM contracts WHERE company.company_id = contracts.company_id);
Here you get company records, and only company table columns, for companies where their id is not present in the contracts table. The use of "1" in the select list is a convention I learned in college, it basically means "I don't care what columns this returns I only care whether a row exists or not - i.e., the where clause is what matters).
David J.