CODEWITHSUNDEEP
mysqlsql
taga
taga

Reputation: 3885

Joining two SQL query results on specific column

I have two queries that work good. They have different columns (different names) except one column 'client_id'. I want to join these two queries on that column 'client_id'. I have tried with union and with join but it does not work. What am I doing wrong? 

   (SELECT i_data.client_id, 
           SUM(i_data.like_by_post) AS total_likes, 
           SUM(i_data.comment_by_post) AS total_comments
      FROM instagram_data AS i_data
  GROUP BY i_data.client_id)
INNER JOIN  
   (SELECT ig_profile.client_id, ig_profile.username, 
           ig_profile.counts_media AS total_posts, 
           ig_profile.followed_by AS followers
      FROM instagram_profile AS ig_profile
  GROUP BY ig_profile.client_id
  ORDER BY ig_profile.current_date) 
        ON i_data.client_id = ig_profile.client_id

Upvotes: 0

Views: 52

Answers (4)

Isisco
Isisco

Reputation: 141

select i_data.client_id, sum(i_data.like_by_post) as total_likes, sum(i_data.comment_by_post) as total_comments, 
ig_profile.client_id, ig_profile.username, ig_profile.counts_media as total_posts, ig_profile.followed_by as followers

from instagram_data as i_data
left join instagram_profile as ig_profile on i_data.client_id = ig_profile.client_id

group by ig_profile.client_id,  i_data.client_id
order by ig_profile.current_date

You can revert to your inner join as it meets your demand.

Upvotes: 0

Deepak Kumar
Deepak Kumar

Reputation: 1

Please specify the alias name for both queries then join them your code will be like this;

select i_data.client_id, sum(i_data.like_by_post) as total_likes, sum(i_data.comment_by_post) as total_comments from instagram_data as i_data inner join(select client_id, username, counts_media as total_posts, followed_by as followers from instagram_profile group by client_id order by current_date) as ig_profile on i_data.client_id = ig_profile.client_id group by i_data.client_id

Upvotes: 0

Raj Purohit
Raj Purohit

Reputation: 241

Your query is not right.

Please try below query :

        SELECT i_data.client_id, 
           SUM(i_data.like_by_post) AS total_likes, 
           SUM(i_data.comment_by_post) AS total_comments,
           ig_profile.client_id, ig_profile.username, 
           ig_profile.counts_media AS total_posts, 
           ig_profile.followed_by AS followers 
      FROM instagram_data AS i_data  
INNER JOIN instagram_profile AS ig_profile 
        ON i_data.client_id = ig_profile.client_id 
  GROUP BY i_data.client_id,ig_profile.client_id 
  ORDER BY ig_profile.current_date;

Upvotes: 0

forpas
forpas

Reputation: 164099

If you want a join:

select * from (
  select i_data.client_id, sum(i_data.like_by_post) as total_likes, 
    sum(i_data.comment_by_post) as total_comments
  from instagram_data as i_data
  group by i_data.client_id
) d inner join (
  select ig_profile.client_id, ig_profile.username, 
    ig_profile.counts_media as total_posts, ig_profile.followed_by as followers
  from instagram_profile as ig_profile
  group by ig_profile.client_id
  order by ig_profile.current_date
) p
on d.client_id = p.client_id

Replace * with the columns that you want to select.
Also the order by clause in the 2nd query is useless and you can remove it. You must sort the result as you want.
Since the 2 queries do not return the same number of columns (with the same data types) you can't use UNION.

Upvotes: 1

Related Questions