How to join result from two tables with same field into one field?

Question

I have tables like this:

   Table1                Table2
   name1 | link_id      name2  |  link_id
   text       1         text         2
   text       2         text         4

And I wanna have result:

name1     name2     link_id
text      text         1
text      text         2
text      text         4

How I can do this?

ADD: Sry, my English in not good. I have device, device_model and device_type tables with duplicate field counter_set_id. I wanna select fields from counter_set with all values of counter_set_id. I need to fetch values only from counter_set_id fields

Now I have this query:

SELECT  `dev`.`counter_set_id`, `mod`.`counter_set_id`, `type`.`counter_set_id`
FROM    `device` AS `dev`
LEFT JOIN   `device_model` AS `mod` ON `dev`.`device_model_id` = `mod`.`id`
LEFT JOIN   `device_type` AS `type` ON `mod`.`device_type_id` = `type`.`id`
WHERE   `dev`.`id` = 4;

This returns 3 columns but I need all values in one column

This is final variant I think:

SELECT  `dev`.`counter_set_id`
FROM        `device` AS `dev` LEFT OUTER JOIN
        `device_model` AS `mod` ON `dev`.`device_model_id` = `mod`.`id`
WHERE   `dev`.`id` = 4 AND
        `dev`.`counter_set_id` IS NOT NULL
UNION
SELECT  `mod`.`counter_set_id`
FROM        `device_model` AS `mod` LEFT OUTER JOIN
        `device` AS `dev` ON `mod`.`id` = `dev`.`device_model_id`
WHERE   `mod`.`counter_set_id` IS NOT NULL;

Answer 1

Based on the sample tables and desired output you provided, it sounds like you might want a FULL OUTER JOIN. Not all vendors implement this, but you can simulate it with a LEFT OUTER join and a UNION to an EXCEPTION join with the tables reversed like this:

Select name1, name2, A.link_id
From table1 A Left Outer Join
     table2 B on A.link_id = B.link_id
Union
Select name1, name2, link_id
From table2 C Exception Join
     table1 D on C.link_id = D.link_id

then your output would be like this:

NAME1   NAME2    LINK_ID
=====   =====    =======
text    <NULL>         1
text    text           2
<NULL>  text           4

Answer 2

At first i thought a join would work but now i'm not sure... Im thinking a union of some type.

in all honesty this is a bad design imo.

select * from table1
union
select * from table2

Answer 3

I'm assuming that you're joining on the link_id field. A normal join would result in four rows being returned (two of which are identical). What you're really asking for isn't combining fields, but getting only the distinct rows.

Just use the distinct keyword:

select distinct t1.name1, t2.name2, t1.link_id
from Table1 as t1
inner join Table2 as t2
    on t1.link_id = t2.link_id