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)