PHPFixing
  • Privacy Policy
  • TOS
  • Ask Question
  • Contact Us
  • Home
  • PHP
  • Programming
  • SQL Injection
  • Web3.0

Friday, October 28, 2022

[FIXED] How to Use an Exclusion Join to Remove Rows In the Right-side Table?

 October 28, 2022     left-join, mysql, outer-join     No comments   

Issue

In MySQL, I am struggling to create a LEFT OUTER JOIN properly to include all rows from the left-side table which are NOT represented in the right-side table (i.e., if a row exists on the right-side table, then exclude it from the result set).

Following is a simple example with two tables: Food (bread, strawberries, carrots) and Allergies (strawberries)

The goal is to show all Food for which no Allergy exists (i.e., the correct result set is a single row for Strawberries)

CREATE TABLE TempFood (ProductName VARCHAR(64));
CREATE TABLE TempAllergies (ProductName VARCHAR(64));
INSERT INTO TempFood (ProductName) VALUES ('Bread'), ('Strawberries'),  ('Carrots');
INSERT INTO TempAllergies (ProductName) VALUES ('Strawberries');

SELECT * FROM TempFood 
LEFT OUTER JOIN TempAllergies ON TempFood.ProductName = TempAllergies.ProductName

I know I'm making a simple mistake and would appreciate guidance on how to fix my JOIN.


Solution

The code for you desired output is a RIGHT OUTER JOIN.

RIGHT OUTER JOIN TempAllergies ON TempFood.ProductName = TempAllergies.ProductName

If you want a table with all foods that have no allergies, the answer is - SELECT * FROM TempFood LEFT JOIN TempAllergies ON TempFood.ProductName = TempAllergies.ProductName WHERE TempAllergies.ProductName IS Null



Answered By - Angus Macdonald
Answer Checked By - David Goodson (PHPFixing Volunteer)
  • Share This:  
  •  Facebook
  •  Twitter
  •  Stumble
  •  Digg
Newer Post Older Post Home

0 Comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Total Pageviews

Featured Post

Why Learn PHP Programming

Why Learn PHP Programming A widely-used open source scripting language PHP is one of the most popular programming languages in the world. It...

Subscribe To

Posts
Atom
Posts
Comments
Atom
Comments

Copyright © PHPFixing