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

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;

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

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

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

What value does link_id 4 have for name1? What value does link_id 1 have for name2?

Try looking up the different JOIN-Types ..

http://dev.mysql.com/doc/refman/5.0/en/join.html

Maybe I misunderstand the question at hand - sorry then.

cheers

If the three columns have the same value, just

SELECT  `type`.`counter_set_id`
FROM ...

Or, better yet, if they have the same value, you can do:

SELECT  `dev`.`counter_set_id`
FROM    `device` AS `dev`
WHERE   `dev`.`id` = 4;

If you want to concatenate them (put them all into the same field), use this:

SELECT  CONCAT(
   CAST(`dev`.`counter_set_id` AS CHAR),
   ',',
   CAST(`mod`.`counter_set_id` AS CHAR),
   ',',
   CAST(`type`.`counter_set_id` AS CHAR))
FROM ...

If you do not want to repeat a field result, use GROUP BY link_id in the end of your query

If you want to show only link_id field then:

SELECT DISTINCT ta.link_id
FROM tblA AS ta
INNER JOIN tblB AS tb
ON ta.link_id = tb.link_id

Also look for CONCAT , CAST and other usefull funcs on mysql manual I hope this help you.

Select all the IDs into a temp table (however that works in MySQL - a CTE in SQL Server), and use that as your joining table.