Remove duplicates when joining tables

Question

I have a news table as follows News: ``` | id | title | description | 1 | Breaking news | bla bla bla | 2 | Heavy snowfall in london | bla bla bla ``` a Type table as follows: ``` | id | type_name | type_code | 1 | weather | 0567 | 2 | city | 0653 ``` and a NewsType table as follows ``` |id | news_id | type_id | created_by | | 1 | 2 | 1 | "John" | | 2 | 2 | 2 | "Alex" | ``` As you can see from the NewsType table that a single news can fall into two or more types. I need to display news corresponding to types. A user might say give me all the news about cities and weather. To display this I am doing something like: ``` select distinct n.* , nt.created_at from news n, newstype nt, type t where n.id = nt.news_id and t.id = nt.type_id order by nt.created_at limit 25 ``` The problem is this query returns the same news twice (I think it's because of the inner join I am doing). What should I change in the query so that if a news is classified as two types, and the user has requested to view…

Accepted Answer

simple solution:

```
select * from news where news_id in (
select news_id 
from NewsType 
where type_id in (the types you want)
)
```

most people would say that you should add a DISTINCT on the news_id on the inner query. You can try that, but Im quite sure it will decrese performance.

Over all, if you think this solution doesnt perform well, you can make the inner query a CTE, which usually behaves better:

```
with my_CTE as(
    select news_id 
    from NewsType 
    where type_id in (the types you want)
)
select * 
from news 
where news_id in (select news_id  from my_CTE)
```

Remove duplicates when joining tables

Solution

Problem