将两个表中的类似数据合并到一个表中

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..

Answer 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>