Merge similar data from two tables into one table

Question

this is an illustration of what I need to do..

TABLE 1

  `----------
    ID     VALUE

    123    A

    123    B

    123    C

    123    D

    123    E

    123    F

    ----------`

TABLE 2

----------
ID        VALUE    STATUS

123        A       POSTED

123        B       POSTED

123        C       CANCEL

----------

OUTPUT

----------
VALUE     ID

A         POSTED

B         POSTED

C         CANCEL

D

E

F

----------

When I search for the ID, everything that has the ID in TABLE 1 needs to be shown when it has a duplicate in table 2 information that does not exist in TABLE1 should merge to be shown in an OUTPUT TABLE... I can only search using the ID

can anyone give me a headstart on how to achieve this in php., mysql is the database ., Im new to this., thanks in advance.. :)

the tables are from two different databases..

Have a look at using `JOIN` in your queries. http://stackoverflow.com/questions/419375/sql-join-differences May help you understand the types that are avilable — Anigel, Jul 26 '13 at 07:12
To ALL: Thanks for the replies . Im gonna try them and research more.. thanks for the headstart :) — user2574957, Jul 26 '13 at 07:39

Praveen Prasannan · Accepted Answer · 2013-07-26T07:28:47.573

1

select t1.id, t1.value,t2.status from table1 t1 
left join table2 t2 on 
t1.value = t2.value where t1.id = <urID>

fiddle

If tables are on two database:

select t1.id, t1.value,t2.status from db1.table1 t1 
left join db2.table2 t2 on 
t1.value = t2.value where t1.id = <urID>

edited Jul 26 '13 at 07:28

answered Jul 26 '13 at 07:14

Praveen Prasannan

7,093
10
50
70

hi ., will this still work even when the tables are from two different databases.? – user2574957 Jul 26 '13 at 07:24
yes.. append database name with a '.' before table. eg db1.table1 – Praveen Prasannan Jul 26 '13 at 07:27
but that would mean two databases are on same server – Bhupendra Jul 26 '13 at 07:33
http://stackoverflow.com/questions/11114197/join-tables-from-two-different-server .. this might help you to join tables in different servers – Praveen Prasannan Jul 26 '13 at 07:43
@Praveen what does t1 means..? – user2574957 Jul 26 '13 at 07:54
alias name for table1 – Praveen Prasannan Jul 26 '13 at 08:24

score 0 · Answer 2 · answered Jul 26 '13 at 07:14

0

You need to connect the two table (your connection criteria is the id and value). So we join the tables on that criteria and select

select table1.id, table1.value, table2.status from table1 inner join table2 on (table1.id=table2.id AND table1.value=table2.value)

after the join in the Where clause you can specify your condition

like,

select table1.id, table1.value, table2.status from table1 inner join table2 on (table1.id=table2.id AND table1.value=table2.value) WHERE table.id = 123

answered Jul 26 '13 at 07:14

Bhupendra

1,725
22
30

HI., will this work even when the tables are from two different databases as well? – user2574957 Jul 26 '13 at 07:21
I don't think so. Two databases would mean two connections. I cannot guarantee that there is no way to query two databases in one query but I would suggest you to do two separate queries and combine data in PHP. I think that will be simpler option – Bhupendra Jul 26 '13 at 07:30
a bit of research point here http://forums.mysql.com/read.php?52,526341,527014#msg-527014 – Bhupendra Jul 26 '13 at 07:32

Nandakumar V · Answer 3 · 2013-07-26T07:29:32.473

0

I think a simple LEFT JOIN is required

SELECT TABLE_1.VALUE,TABLE_2.STATUS FROM TABLE_1 LEFT JOIN TABLE_2 ON TABLE_1.ID = TABLE_2.ID

SELECT D1.TABLE_1.VALUE, D2.TABLE_2.STATUS FROM D1.TABLE_1 LEFT JOIN D2.TABLE_2 ON D1.TABLE_1.ID = D2.TABLE_2.ID

edited Jul 26 '13 at 07:29

answered Jul 26 '13 at 07:15

Nandakumar V

4,317
4
27
47

HI., will this work even when the tables are from two different databases as well? – user2574957 Jul 26 '13 at 07:16
I haven't used this but from these SO posts it seems possible [1](http://stackoverflow.com/questions/17310706/query-with-a-table-from-2-databases) , [2](http://bytes.com/topic/php/answers/721490-connecting-two-mysql-tables-different-databases) – Nandakumar V Jul 26 '13 at 07:25

score 0 · Answer 4 · answered Jul 26 '13 at 07:16

0

Try this pls

Select * from table1 as tbl1 left join table2 as tbl2 on tbl1.id = tbl2.id and tbl1.value = tbl2.value

answered Jul 26 '13 at 07:16

zxc

1,504
3
13
32

Merge similar data from two tables into one table

4 Answers4