Geek Answers Handbook

How to join the result of two tables with one field in 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 want to get the result:

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

How can i do this?

ADD: Sry, my English is not very good. I have device tables, device_model and device_type with a duplicate counter_set_id field. I want to select fields from counter_set with all counter_set_id values. I need to get 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 the values ​​in one column

This is the final version, 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;
+5
source share
7 answers

, , , , , . , LEFT OUTER UNION EXCEPTION :

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

:

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

, link_id. ( ). , , , .

distinct:

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
+3

, , ... Im - .

, imo.

select * from table1
union
select * from table2
+3

link_id 4 name1? link_id 1 name2?

JOIN.

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

, - , .

+1

,

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

, , , :

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

( ), :

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

, GROUP BY link_id

link_id, :

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

CONCAT, CAST mysql , .

+1

Select all identifiers in a temporary table (however this works in MySQL - CTE in SQL Server) and use this as a connection table.

+1
source

More articles:


All Articles