Ignore repeated rows with SELECT statement

Question

I have a table like below.

One table for storing fruits and one for its type.

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');

Now I want a output like below

  Fruit_id    FruitName
    101       Apple
    102       Mango
    103       Lemon
    104       Grape

I used INNER JOIN but its getting repeated rows for apple, lemon and Grapes

SELECT fruits.* 
FROM fruits INNER JOIN type 
ON type.fruit_id = fruits.fruit_id

SELECT fruits.* FROM fruits inner join type on type.fruit_id = fruits.fruit_id — user1093513, Sep 27 '12 at 06:50
I am Sorry My Mistake i think I havent properly formatted my Quetion — user1093513, Sep 27 '12 at 07:03
Actually your requirement is unclear. So every answers may give you different result. — Himanshu, Sep 27 '12 at 07:05

Himanshu · Answer 1 · 2012-09-27T07:09:53.327

3

You can use DISTINCT to ignore duplicate rows like this one:

SELECT DISTINCT F.*
FROM fruits f
INNER JOIN type t ON F.Fruit_id = t.Fruit_id;

See this SQLFiddle

edited Sep 27 '12 at 07:09

answered Sep 27 '12 at 06:59

Himanshu

31,810
31
111
133

With INNER JOIN the result is the same and I think is more efficient just look to this question on stackoverflow (http://stackoverflow.com/questions/2726657/inner-join-vs-left-join-performance-in-sql-server). That doesn't means that your answer is completely great :) – Amedio Sep 27 '12 at 07:06
I will correct myself "... That doesn't mean that your answer is wrong, is completely great..." – Amedio Sep 27 '12 at 07:45
1

I told the opposite and I was saying what I wanted to say, that your answer is great... :) – Amedio Sep 27 '12 at 07:51
@Amedio Actually I was confused first about OP's problem. And got it after OP posted his/her effort. – Himanshu Sep 27 '12 at 07:54

score 2 · Answer 2 · answered Sep 27 '12 at 06:53

2

select f.fruit_id,f.FruitName from 
fruits where fruit_id in(select distinct fruit_id from type)

answered Sep 27 '12 at 06:53

AnandPhadke

13,160
5
26
33

score 2 · Answer 3 · answered Sep 27 '12 at 06:56

2

write sql like you would say it :-)

"show me all fruits where a type exists":

select *
from fruits
where exists (
    select *
    from types 
    where types.fruit_id = fruits.fruit_id
)

answered Sep 27 '12 at 06:56

RomanKonz

1,027
1
8
15

score 1 · Answer 4 · edited Sep 27 '12 at 09:08

1

SELECT f.fruit_id ,f.fruit_name 
FROM fruits f 
INNER JOIN type t ON f.fruit_id=t.fruit_id 
GROUP BY f.fruit_id,f.fruit_name

or

SELECT DISTINCT f.fruit_id ,f.fruit_name 
FROM fruits f 
INNER JOIN type t ON f.fruit_id=t.fruit_id

edited Sep 27 '12 at 09:08

Himanshu

31,810
31
111
133

answered Sep 27 '12 at 06:59

Mohit

39
1
11

score 1 · Answer 5 · answered Sep 27 '12 at 07:00

You are getting repeated results because at the moment to do the inner join, it gives you the pairs between data, so is normal that a fruit appears more than once.

For what you have, maybe is possible to use DISTINCT(fruits.*) in the SELECT statement to solve you problem.

Hope this helps.

Ignore repeated rows with SELECT statement

5 Answers5

See this SQLFiddle