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

To prevent the repetition of fruit name the queries are as follows

SELECT f.fruit_id, f.fruit_name
  FROM fruits f
 WHERE f.fruit_id in(SELECT DISTINCT fruit_id
                       FROM type);

SELECT DISTINCT t.Fruit_id, fruit_name
  FROM fruits f RIGHT OUTER JOIN
       type t ON F.Fruit_id = t.Fruit_id;

SELECT fruits.*
  FROM fruits inner join
       type on type.fruit_id = fruits.fruit_id
  GROUP BY type.fruit_id;
Posted in SQL.

Leave a reply