Two tables

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

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

  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

 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

 SELECT fruits 
   FROM tblEatables 
  WHERE EatID  NOT IN (SELECT Eatables_Id 
                         FROM tblConfirm_Eatables 
                        WHERE   Edible_Status = 1)           

Leave a reply