如何使用连接编写确切的查询。请指导我 [英] How to write the exact query using joins. Please guide me

问题描述

- 情景



- 第一桌

从主要选择*



- 其中A IS 10的地址ID ... B IS 20的地址ID .... C IS 30的地址ID

- 表20中有两个集线器ID 136

address_id hub_id位置距离

10 20 A NULL

20 20 B NULL

30 20 C NULL

40 20 D NULL

10 136 A NULL

20 136 B NULL

30 136 C NULL
40 136 D NULL



------------------------- -------------------------------------------------- ------------------



- 秒表

从距离选择*



- A的地址是10，距离枢纽136公里50公里

- 其中，地址ID为B距离枢纽136公里50公里



address_id hub_id位置距离

10 136 A 50

20 136 B 75

30 136 C 85

40 136 D 95







=================我想要的所需输出======== ====================================

- 现在我必须在主表（第一个表）中选择HUB_ID 136.

- 所需输出为



ADDRESS_ID HUB_ID位置距离

10 136 A 50

20 136 B 75

30 136 C 85

40 136 D 95





- 如果我尝试选择集线器为20距离应该为空，因为距离不在第二个表（距离表）中的hub_id 20



ADDRESS_ID HUB_ID位置距离

10 136 A NULL

20 136 B NULL

30 136 C NULL

40 136 D NULL





======================== ================================================== ==================



- 我试图用这种方式写作

- 这是工作

选择m.address_id，m.hub_id，m.location，d.distance

from main m

加入距离d on m.address_id = d.address_id

其中m.hub_id = 136



address_id hub_id位置距离

10 136 A 50

20 136 B 75

30 136 C 85

40 136 D 95





- 但如果我选择20 - 这不是所需的输出，因为距离应该为空

选择m.address_id ，m.hub_id，m.location，d.distance

from main m

加入距离d on m.address_id = d.address_id

其中m.hub_id = 20



address_id hub_id位置距离

10 20 A 50

20 20 B

20 20 C 85

20 20 D 95





- 我正在尝试这个...但没有结果

选择m.address_id，m.hub_id，m.location，d.distance
$ m $ b来自主m

左加入距离d on m.address_id = d.address_id

其中m.hub_id = 20

和d.hub_id = 20







请帮忙这是紧急...

解决方案

当您加入表时，请确保包含需要在ON子句中链接的所有列。你的WHERE子句应该只包含主表的过滤器：

 选择 m.address_id，m.hub_id，m.location，d.distance 
 来自 main m 
  left   join 距离d  on  m.address_id = d.address_id  AND  m.hub_id = d.hub_id  AND  m.location = d.location 
  where  m.hub_id = 20 

请在撰写问题时使用标签

;   cte（address_Id，hub_id，location，distance）
  as  
（
 选择 m.address_id 
，m.hub_id 
，m.location 
，d.distance 
 来自 main   m 
  left   join 距离  d 
  on  m.address_id = d.address_id 
 和 m.hub_id = d.hub_id 
 和 m.location = d.location 
）
 
 选择 * 来自 cte 
 其中 hub_id =  20  

--Scenario

--first table
select * from main

--here the addressid of A IS 10...addressid of B IS 20....addressid of C IS 30
--two hub id is there in the table 20 and 136
address_id hub_id location distance
10 20 A NULL
20 20 B NULL
30 20 C NULL
40 20 D NULL
10 136 A NULL
20 136 B NULL
30 136 C NULL
40 136 D NULL

---------------------------------------------------------------------------------------------

--second table
select * from distance

--HERE THE ADDRESSID OF A That is 10 is 50 km from hub 136
--HERE the addressid OF B That is 20 is 50 km from hub 136

address_id hub_id location distance
10 136 A 50
20 136 B 75
30 136 C 85
40 136 D 95



=================Desired output which i want ============================================
--now i have to select in main table(first table) with HUB_ID 136 .
--desired output as

ADDRESS_ID HUB_ID LOCATION DISTANCE
10 136 A 50
20 136 B 75
30 136 C 85
40 136 D 95


--and if i try to select the hub as 20 distance should be null as distance is not there for hub_id 20 in second table(distance table)

ADDRESS_ID HUB_ID LOCATION DISTANCE
10 136 A NULL
20 136 B NULL
30 136 C NULL
40 136 D NULL


============================================================================================

-- i was trying to write in this way
--ok this is working
select m.address_id,m.hub_id,m.location,d.distance
from main m
join distance d on m.address_id=d.address_id
where m.hub_id=136

address_id hub_id location distance
10 136 A 50
20 136 B 75
30 136 C 85
40 136 D 95


-- but if i select for 20--this is not desired output as distance should be null
select m.address_id,m.hub_id,m.location,d.distance
from main m
join distance d on m.address_id=d.address_id
where m.hub_id=20

address_id hub_id location distance
10 20 A 50
20 20 B 75
30 20 C 85
40 20 D 95


-- i was trying this ...but no result
select m.address_id,m.hub_id,m.location,d.distance
from main m
left join distance d on m.address_id=d.address_id
where m.hub_id=20
and d.hub_id=20



please help this is urgent...

解决方案

When you join the tables make sure you include all columns that need to be linked in your ON clause. Your WHERE clause should contain only the filter for the main table:

select m.address_id,m.hub_id,m.location,d.distance
from main m
left join distance d on m.address_id=d.address_id AND m.hub_id=d.hub_id AND m.location = d.location
where m.hub_id=20

And please, use the tags when you writing the questions

;with cte(address_Id,hub_id,location,distance)
as
(
select  m.address_id
        ,   m.hub_id
        ,   m.location
        ,   d.distance
            from main as m
            left join Distance as d
            on m.address_id = d.address_id
            and m.hub_id = d.hub_id
            and m.location = d.location
)

select * from cte
where hub_id = 20

这篇关于如何使用连接编写确切的查询。请指导我的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！