How to remove duplicates in a sql JOIN while getting multiple rows from multiple tables

user_9814486 0
Viewed 46

My project is about a blog where people post articles and get awarded weekly according to the post reach for the last week and ads and so on ...

for a certain situation, I need a query that returns multiple rows from three tables, the problem is that when i use JOIN there are multiple duplicates and i can't seem to find a solution to remove them.

The tables are structured as follows:

Table 1 : Users

  ID      | Name      | Age

  1       | Mark      | 16
  2       | Michael   | 21

Table 2: Posts

 userID      | postID   | Title

     1       | A1       | Inflation : A simplified Approach
     1       | A2       | The "un-winnable" game of politics
     2       | A3       | What is Bourgeoisie ?
     2       | A4       | The radical views of Carl Marx
     2       | A5       | Anna Karenina, or the greatest novel in history

Table 3: Revenue

   userID     | weekRevenue  

      1       | $0.2    
      1       | $0.14       
      1       | $0.91 
      1       | $1.72 
      2       | $0.99

All I want is a query that returns the following:

 ID    | postID    | weekRevenue

 1     | A1        | $0.2
 1     | A2        | $0.14
 1     | 'Null'    | $0.91
 1     | 'Null'    | $1.72
 2     | A3        | $0.99
 2     | A4        | 'Null'
 2     | A5        | 'Null'

i.e. : return values from all tables without duplicating any value and without linking any field from the two tables but to the ID field in the Users table

is that even possible ? , if not, what is the approach to reach anything close to what I'm asking for?

My approach (which obviously failed) was to use JOIN and here is my query

Select A.ID,B.postID,C.weekRevenue
from Users A
left join Posts B
on A.ID=B.userID
left join Revenue C
on A.ID=C.userID;

and the return was :

 ID    | postID    | weekRevenue

 1     | A1        | $0.2
 1     | A1        | $0.14
 1     | A1        | $0.91
 1     | A1        | $1.72
 1     | A2        | $0.2
 1     | A2        | $0.14
 1     | A2        | $0.91
 1     | A2        | $1.72
 2     | A3        | $0.99
 2     | A4        | $0.99
 2     | A5        | $0.99

I also tried Right JOIN , inner and combinations of them with no significant change in the returned table.

any suggestion would be helpful, many thanks in advance.

mysql sql database
1 Answers

You don't want the results in a "relational" format, because the columns in a row don't have a relationship to each other. However, you can do what you want using row_number() and union all:

select id, max(postid) as postid, max(weekrevenue) as weekrevenue
from ((select id, postid, null as weekrevenue,
              row_number() over (partition by id order by postid) as seqnum
       from posts
      ) union all
      (select id, null as postid, weekrevenue,
              row_number() over (partition by id order by weekrevenue) as seqnum
       from revenue
      )
     ) pr
group by id, seqnum;
user_11440350
Related