How to connect two table using date and without any conditon?

Question

I want to connect two table whose schema is like below...!!

Table :

team_posts

  id
  team_id
  text
  location_name
  stars_count
  comments_count
  created_at
  updated_at

team_feed_activites

    id
    team_id
    operation
    created_at
    updated_at

And the table values are

team_posts

id | team_id | text | location_name | stars_count | comments_count |        created_at      |       updated_at 

1  |    23   | crow |    india      |      1      |       2        |   2016-03-18 06:30:00  |   2016-03-18 06:30:00
2  |    24   | bird |    india      |      1      |       2        |   2016-03-18 06:30:00  |   2016-03-19 06:30:00
3  |    25   | span |    india      |      33     |       2        |   2016-03-18 06:30:00  |   2016-03-18 06:30:00
4  |    25   | bang |    india      |      3      |       2        |   2016-03-18 06:30:00  |   2016-03-12 06:30:00
5  |    27   | crow |    india      |      1      |       2        |   2016-03-18 06:30:00  |   2016-03-18 06:30:00
6  |    23   | crow |    india      |      1      |       2        |   2016-03-18 06:30:00  |   2016-01-18 06:30:00
7  |    23   | crow |    india      |      7      |       2        |   2016-03-18 06:30:00  |   2016-03-18 06:30:00
8  |    23   | hen  |    india      |      1      |       2        |   2016-03-18 06:30:00  |   2016-03-01 06:30:00
9  |    23   | mani |    india      |      1      |       2        |   2016-03-18 06:30:00  |   2016-03-18 06:30:00
10 |    23   | dog  |    india      |      1      |       2        |   2016-03-18 06:30:00  |   2016-03-18 06:30:00
11 |    29   | crow |    india      |      10     |       2        |   2016-03-18 06:30:00  |   2016-02-26 06:30:00
12 |    29   | god  |    india      |      1      |       2        |   2016-03-18 06:30:00  |   2016-03-18 06:30:00
13 |    29   | pen  |    india      |      44     |       2        |   2016-03-18 06:30:00  |   2016-03-25 06:30:00


 team_feed_activites

    id  | team_id |  operation      |   created_at        |         updated_at

    1   |    10   |  established    | 2016-03-18 06:30:00 | 2016-03-18 06:30:00
    2   |    23   |  established    | 2016-03-19 06:30:00 | 2016-03-19 06:30:00
    3   |    23   |    modified     | 2016-03-19 06:30:00 | 2016-03-19 06:30:00
    4   |    24   | captian changed | 2016-03-19 06:30:00 | 2016-03-19 06:30:00
    5   |    23   | captian added   | 2016-03-19 06:30:00 | 2016-03-19 06:30:00
    6   |    27   |      won        | 2016-03-19 06:30:00 | 2016-03-19 06:30:00
    7   |    23   |      won        | 2016-03-19 06:30:00 | 2016-03-19 06:30:00
    8   |    23   |   paricipated   | 2016-03-19 06:30:00 | 2016-03-19 06:30:00
    9   |    23   |       lost      | 2016-03-19 06:30:00 | 2016-03-19 06:30:00
   10   |    23   |   changed pic   | 2016-03-19 06:30:00 | 2016-03-19 06:30:00
   11   |    23   |  addded image   | 2016-03-19 06:30:00 | 2016-03-19 06:30:00
   12   |    30   |  established    | 2016-03-19 06:30:00 | 2016-03-19 06:30:00

Now what i need is to create a view where this table is merged into table based on updated_at date. Such that i can see what is the team progress day by day so that i can use that as team feeds..Could me some one help me how to do that..!!! Thanks in advance ..!!!

what is the granularity of your comparison ? a day ? an hour ? If it's a day you should use DATE(updated_at) in the WHERE clause and use a GROUP BY clause somewhere — quazardous, Mar 23 '16 at 07:07
every second since activities or posts will be done every second ... — Mani Kandan, Mar 23 '16 at 07:12
What you want is called à full Outer Join : you want all the lines from jeft and right table. http://stackoverflow.com/questions/4796872/full-outer-join-in-mysql — quazardous, Mar 23 '16 at 07:36

score 0 · Answer 1 · answered Mar 23 '16 at 06:02

0

Just use simple INNER JOIN to combine data from two tables based on updated_at field

SELECT * FROM team_posts tp 
   INNER JOIN team_feed_activites tfa ON tp.updated_at = tfa.updated_at;

answered Mar 23 '16 at 06:02

alexander.polomodov

5,396
14
39
46

tp.updated_at = tfa.updated_at if i comare two table dates then i will be not getting other activities of the team, i will be getting the activites or posts of the team only if two table dates are same.. – Mani Kandan Mar 23 '16 at 06:07
I respect your answer Sir, But I think this will only merge data that has a `updated_at` and will skip the others. – rmondesilva Mar 23 '16 at 06:07

score 0 · Answer 2 · answered Mar 23 '16 at 06:09

0

you can use this query SELECT tp.field,tfa.field FROM team_posts tp,team_feed_activites tfa

but use carefully this will give duplicate records some time and assign new name for each field because there is same name in field so it will replace it

field name assign : tp.id as tp_id

answered Mar 23 '16 at 06:09

Krunal Patel

59
2
6

because if you will not include any comment then it will gives duplicate records. please give me common field from both tables so i can give you new query that will work 100% – Krunal Patel Mar 23 '16 at 06:55
we don't have common column incase if we would have had same column then i would have not asked this question at all.. – Mani Kandan Mar 23 '16 at 07:13

rmondesilva · Answer 3 · 2016-03-23T13:31:11.177

0

Connect using LEFT JOIN and RIGHT JOIN then join them using UNION ALL You can try this.

SELECT * 
FROM
(SELECT a.* FROM team_posts a 
       LEFT JOIN 
       team_feed_activities b 
       ON a.updated_at=b.updated_at 
UNION ALL 
SELECT a.* FROM team_posts a 
       RIGHT JOIN 
       team_feed_activities b 
       ON a.updated_at=b.updated_at) subquery 
ORDER BY updated_at DESC

NOTE: The two(sub-queries) SELECT columns MUST have the same number of columns. Example:

SELECT a.*, b.operation

on second, it MUST be the same

SELECT a.*, b.operation or SELECT a.*, b.team_id

as long as both tables will display same number of columns.

edited Mar 23 '16 at 13:31

answered Mar 23 '16 at 06:15

rmondesilva

1,732
3
17
29

It merges the table but am not getting the sorting based on update at. Instead am getting the updated_at column twice one from team posts table and other from team_feed_activites – Mani Kandan Mar 23 '16 at 06:23
@ManiKandan I updated my answer. Please check it out. – rmondesilva Mar 23 '16 at 06:46
I get error as "Every derived table must have its own alias" . I guess this error occurs since in order ny updated_at we didn't give any alias. – Mani Kandan Mar 23 '16 at 07:22
@ManiKandan my bad. I had some typos in parenthesis. I updated again and it should do the job. Check it out please. – rmondesilva Mar 23 '16 at 13:26

score -1 · Accepted Answer · answered Mar 26 '16 at 07:31

I tried the below code and i got the output as i accepted.

create view team_feed AS
    select tp.id as post_id,
           tp.team_id as post_team_id,
           tp.text,
           tp.location_name,
           tp.stars_count,
           tp.comments_count,
           tp.created_at as post_created_at,
           tp.updated_at as post_updated_at,
           '' as team_feed_activites_id,
           '' as team_feed_activites_team_id,
           '' as team_feed_activites_operation,
           '' as team_feed_activites_created_at,
           '' as team_feed_activites_updated_at,
           tp.updated_at as event_updated_at
    from team_posts as tp
    UNION
    select '' as post_id,
           '' as post_team_id,
           '' as text,
           '' as location_name,
           '' as stars_count,
           '' as comments_count,
           '' as post_created_at,
           '' as post_updated_at,
           tfa.id as team_feed_activites_id,
           tfa.team_id as team_feed_activites_team_id,
           tfa.operation as team_feed_activites_operation,
           tfa.created_at as team_feed_activites_created_at,
           tfa.updated_at as team_feed_activites_updated_at,
           tfa.updated_at as event_updated_at
    from team_feed_activites as tfa

In above query i made unique column as event_updated_at based on table selection and made this as view. And to get data from this i used below query.

select post_id,
   post_team_id,
   text,
   location_name,
   stars_count,
   comments_count,
   team_feed_activites_id,
   team_feed_activites_team_id,
   team_feed_activites_operation
from team_feeds
order by event_updated_at ASC

How to connect two table using date and without any conditon?

4 Answers4