Select query paginated without duplicates with bridge table-CodePudding

I have a table news, a table category and a table bridge.

I want to select news with category ID = 1 or 2 or 3.

Example:

SELECT *
FROM NEWS
  INNER JOIN BRIDGE ON NEWS.ID = BRIDGE.ID_NEWS
WHERE BRIDGE.ID_CATEGORY IN (1,2,3)

Problem is that when i do a select i have the second news (NEWS.ID = 2) duplicated.

I can't use distinct and anyway distinct not resolve the problem when i use pagination.

SCENARIO:

TABLE NEWS

|ID|TITLE|
|1 |First new|
|2 |second new|
|3 |third new|

TABLE CATEGORY

|ID|TITLE|
|1 |Great|
|2 |Bad  |
|3 |Green|

TABLE BRIDGE

|ID_NEWS|ID_CATEGORY|
|1      |1
|2      |1
|2      |2
|3      |3

Results are:

First new, second new, second new, third new.

I want:

First new, second new, third new.

How can I have no duplicates?

CodePudding user response：

If you want all the columns from NEWS table, you could use:

SELECT n.ID,
       n.TITLE
FROM NEWS n
INNER JOIN BRIDGE b ON n.ID = b.ID_NEWS
WHERE b.ID_CATEGORY IN (1,2,3)
GROUP BY n.ID,n.TITLE

GROUP BY n.ID,n.TITLE will take care of duplicates.

Another option would be using distinct if you want only NEWS.TITLE unique values for the specified categories.

SELECT distinct n.TITLE
FROM NEWS n
INNER JOIN BRIDGE b ON n.ID = b.ID_NEWS
WHERE b.ID_CATEGORY IN (1,2,3)

If you want all columns from NEWS and BRIDGE and still want distinct result on NEWS.TITLE , you have to apply an aggregate function, for example min or max on BRIDGE.ID_CATEGORY to select only one row per TITLE.

Try:

SELECT n.ID,
       n.TITLE,
       max(b.ID_CATEGORY) as max_ID_CATEGORY
FROM NEWS n
INNER JOIN BRIDGE b ON n.ID = b.ID_NEWS
WHERE b.ID_CATEGORY IN (1,2,3)
GROUP BY n.ID,n.TITLE

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=afa8fc969636dbe7b7421c51db20cc75