Left Outer Join Query

Posted on by | Leave a reply

Two tables

01
02
03
04
05
06
07
08
09
10
CREATE TABLE tblEatables (
    `EatId` int UNSIGNED PRIMARY AUTO_INCREMENT,
    `Fruits` varchar(9) NOT NULL
) Engine=InnoDB;
 
CREATE TABLE tblConfirm_Eatables (
    Eatables_Id INT UNSIGNED,
    Edible_Status INT,
    FOREIGN KEY Eatables_Id REFERENCES tblEatables (EatId)
) Engine=InnoDB;

Rows in Tables

01
02
03
04
05
06
07
08
09
10
11
12
13
INSERT INTO tblEatables(`EatId`, `Fruits`)
       VALUES(1, 'Apples'),
             (2, 'Oranges'),
             (3, 'Papaya'),
             (4, 'Jackfruit'),
             (5, 'Pineapple'),
             (6, 'Mango');
 
INSERT INTO tblConfirm_Eatables
    VALUES(1,0),
          (2,1),
          (3,0),
          (4,0);

The Result Should be

  Fruits
  Apple
  Papaya
  Jackfruit
  Pineapple
  Mango

Query1

1
2
3
4
5
SELECT Fruits
  FROM tblEatables AS E LEFT JOIN
       tblConfirm_Eatables AS CE ON E.EatID = CE.Eatables_ID
 WHERE CE.Edible_Status = 0 OR
       CE.Edible_Status IS NULL

Query 2

1
2
3
4
SELECT e.EatId,e.Fruits
  FROM @tblEatables e LEFT JOIN
       @tblConfirm_Eatables ce ON e.EatId = ce.Eatbles_Id
 WHERE ce.Edible_Status  = 0 OR ce.Edible_Status IS Null

Query 3

1
2
3
4
5
SELECT fruits
  FROM tblEatables
 WHERE EatID  NOT IN (SELECT Eatables_Id
                        FROM tblConfirm_Eatables
                       WHERE   Edible_Status = 1)          

Leave a reply