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 the same two types of news, I get only single news item? instead of two!

Answer 1

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)

Answer 2

A group by is another approach to this:

select n.id, n.title, n.description, max(nt.created_at)
      from news n, newstype nt, type t where
      n.id = nt.news_id and
      t.id = nt.type_id
      group by n.id, n.title, n.description
      order by nt.created_at
      limit 25