{"id":618,"date":"2012-07-27T09:26:58","date_gmt":"2012-07-27T09:26:58","guid":{"rendered":"http:\/\/codeatelier.wordpress.com\/?p=148"},"modified":"2016-09-10T13:58:10","modified_gmt":"2016-09-10T13:58:10","slug":"left-outer-join-query","status":"publish","type":"post","link":"https:\/\/codethataint.com\/blog\/left-outer-join-query\/","title":{"rendered":"Left Outer Join Query"},"content":{"rendered":"<p><strong>Two tables<\/strong><\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nCREATE TABLE tblEatables (\r\n    `EatId` int UNSIGNED PRIMARY AUTO_INCREMENT,\r\n    `Fruits` varchar(9) NOT NULL\r\n) Engine=InnoDB;\r\n\r\nCREATE TABLE tblConfirm_Eatables (\r\n    Eatables_Id INT UNSIGNED,\r\n    Edible_Status INT,\r\n    FOREIGN KEY Eatables_Id REFERENCES tblEatables (EatId)\r\n) Engine=InnoDB;\r\n<\/pre>\n<p><strong>Rows in Tables<\/strong><\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nINSERT INTO tblEatables(`EatId`, `Fruits`)\r\n       VALUES(1, 'Apples'),\r\n             (2, 'Oranges'),\r\n             (3, 'Papaya'),\r\n             (4, 'Jackfruit'),\r\n             (5, 'Pineapple'),\r\n             (6, 'Mango');\r\n\r\nINSERT INTO tblConfirm_Eatables\r\n    VALUES(1,0),\r\n          (2,1),\r\n          (3,0),\r\n          (4,0);\r\n<\/pre>\n<p>The Result Should be<\/p>\n<pre>\r\n  Fruits\r\n  Apple\r\n  Papaya\r\n  Jackfruit\r\n  Pineapple\r\n  Mango\r\n<\/pre>\n<p><strong>Query1<\/strong><\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\n  SELECT Fruits\r\n    FROM tblEatables AS E LEFT JOIN \r\n         tblConfirm_Eatables AS CE ON E.EatID = CE.Eatables_ID\r\n   WHERE CE.Edible_Status = 0 OR \r\n         CE.Edible_Status IS NULL\r\n<\/pre>\n<p><strong>Query 2<\/strong><\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\n SELECT e.EatId,e.Fruits\r\n   FROM @tblEatables e LEFT JOIN \r\n        @tblConfirm_Eatables ce ON e.EatId = ce.Eatbles_Id\r\n  WHERE ce.Edible_Status  = 0 OR ce.Edible_Status IS Null\r\n<\/pre>\n<p><strong>Query 3<\/strong><\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\n SELECT fruits \r\n   FROM tblEatables \r\n  WHERE EatID  NOT IN (SELECT Eatables_Id \r\n                         FROM tblConfirm_Eatables \r\n                        WHERE   Edible_Status = 1)           \r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>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, &#8216;Apples&#8217;), (2, &#8216;Oranges&#8217;), (3, &#8216;Papaya&#8217;), (4, &#8216;Jackfruit&#8217;), (5, &#8216;Pineapple&#8217;), (6, &#8216;Mango&#8217;); INSERT INTO&hellip; <a href=\"https:\/\/codethataint.com\/blog\/left-outer-join-query\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14,20],"tags":[],"class_list":["post-618","post","type-post","status-publish","format-standard","hentry","category-mysql","category-queries"],"_links":{"self":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts\/618","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/comments?post=618"}],"version-history":[{"count":3,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts\/618\/revisions"}],"predecessor-version":[{"id":1579,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts\/618\/revisions\/1579"}],"wp:attachment":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/media?parent=618"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/categories?post=618"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/tags?post=618"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}