如何得到这样的结果？

[戈壁上的月光]

有两个表 node,node_company
数据如下node id 为主键
id name type
1 001 A
2 002 B
3 003 A
node_company
id company
1 cpy1
1 cpy2
2 cpy1
id 和 company 字段都是一对多的关系
要得到的结果：按类型查找
name type company
001 A cpy1
001 A cpy2
003 B

这是一种情况，还有一种情况是按类型查询的时候node表所包含的id 都有对应的company
请问要怎么查询？

[戈壁上的月光]

加了distinct 有什么用呢 ，name列还是有重复项？

[Zoezs]

1.
select a.name,a.type,b.company from
node a left join node_company  b
on a.id=b.id

2.
select a.name,a.type,b.company from
node a  join node_company  b
on a.id=b.id

[olddown]

第一种用内联
select a.name,a.type,b.company from node a inner join node_company b on a.id=b.id
第二种左连
select a.name,a.type,b.company from node a left outer join node_company b on a.id=b.id

[zhengduan964532]

create table nod (id int,name varchar(100),type varchar(100))
insert nod
select '1','001','A'union all
select '2','002','B'union all
select '3','003','A'

create table nod_company(id int,company varchar(100))
insert nod_company
select '1','cpy1'union all
select '1','cpy2'union all
select '2','cpy1'

select distinct a.name,a.type,b.company from nod a left join nod_company b on a.id=b.id order by a.type

[戈壁上的月光]

还有个TYPE='' 条件！ 不用加的吗

[戈壁上的月光]

还有个搜索条件是 TPYE='' 满足该条件下的结果

[ai_li7758521]

CREATE TABLE Node (ID INT,NAME VARCHAR(10),TYPE VARCHAR(10))
INSERT Node
SELECT '1','001','A' UNION ALL
SELECT '2','002','B' UNION ALL
SELECT '3','003','A'

CREATE TABLE Node_Company(ID INT,Company VARCHAR(10))
INSERT Node_Company
SELECT '1','CPY1'UNION ALL
SELECT '1','CPY2'UNION ALL
SELECT '2','CPY1'


1.
SELECT DISTINCT [name],[type],company 
FROM Node_Company a left join Node b 
	ON A.ID=B.ID 
--WHERE Type='A'
ORDER BY B.TYPE

name       type       company
---------- ---------- ----------
001        A          CPY1
001        A          CPY2
002        B          CPY1

(3 行受影响)

2.
SELECT DISTINCT [name],[type],company 
FROM Node a left join Node_Company b 
	ON A.ID=B.ID 
--WHERE Type='A'
ORDER BY A.TYPE

name       type       company
---------- ---------- ----------
001        A          CPY1
001        A          CPY2
003        A          NULL
002        B          CPY1

(4 行受影响)