{"id":650,"date":"2012-09-27T09:21:18","date_gmt":"2012-09-27T09:21:18","guid":{"rendered":"http:\/\/codeatelier.wordpress.com\/?p=318"},"modified":"2016-09-10T15:23:27","modified_gmt":"2016-09-10T15:23:27","slug":"alternate-to-inner-join-which-prevents-repetation-of-records","status":"publish","type":"post","link":"https:\/\/codethataint.com\/blog\/alternate-to-inner-join-which-prevents-repetation-of-records\/","title":{"rendered":"Alternate to Inner Join which prevents repetation of Records"},"content":{"rendered":"
\r\ncreate table fruits(fruit_id int, fruit_name VARCHAR(255));  \r\ncreate table type(fruit_id int, status VARCHAR(255));\r\n\r\nINSERT INTO fruits(fruit_id, fruit_name) \r\n            values(101, 'Apple'),\r\n                  (102, 'Mango'),\r\n                  (103, 'Lemon'),\r\n                  (104, 'Grape'),\r\n                  (105, 'Orange');\r\n\r\nINSERT INTO type(fruit_id, status) \r\n          values(101, 'Edible'),\r\n                (101, 'Sweet'),\r\n                (102, 'Edible'),\r\n                (103, 'Edible'),\r\n                (103, 'Salty'),\r\n                (103, 'Sour'),\r\n                (104, 'Sour');\r\n<\/pre>\n
When i Use Inner Join as Below the Output would be<\/p>\n
\"\"<\/p>\n
To prevent the repetition of fruit name the queries are as follows<\/p>\n
\r\nSELECT f.fruit_id, f.fruit_name\r\n  FROM fruits f\r\n WHERE f.fruit_id in(SELECT DISTINCT fruit_id\r\n                       FROM type);\r\n\r\nSELECT DISTINCT t.Fruit_id, fruit_name\r\n  FROM fruits f RIGHT OUTER JOIN\r\n       type t ON F.Fruit_id = t.Fruit_id;\r\n\r\nSELECT fruits.*\r\n  FROM fruits inner join\r\n       type on type.fruit_id = fruits.fruit_id\r\n  GROUP BY type.fruit_id;\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
create table fruits(fruit_id int, fruit_name VARCHAR(255)); create table type(fruit_id int, status VARCHAR(255)); INSERT INTO fruits(fruit_id, fruit_name) values(101, ‘Apple’), (102, ‘Mango’), (103, ‘Lemon’), (104, ‘Grape’), (105, ‘Orange’); INSERT INTO type(fruit_id, status) values(101, ‘Edible’), (101, ‘Sweet’), (102, ‘Edible’), (103, ‘Edible’), (103, ‘Salty’), (103, ‘Sour’), (104, ‘Sour’); When i Use Inner Join as Below the Output would be… Continue reading →<\/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":[20],"tags":[],"class_list":["post-650","post","type-post","status-publish","format-standard","hentry","category-queries"],"_links":{"self":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts\/650","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=650"}],"version-history":[{"count":2,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts\/650\/revisions"}],"predecessor-version":[{"id":1612,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts\/650\/revisions\/1612"}],"wp:attachment":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/media?parent=650"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/categories?post=650"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/tags?post=650"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}