您的位置: 首页 > 技术问答 > 如何在2个表中选择具有不同连接条件的一列

如何在2个表中选择具有不同连接条件的一列

分类: 技术问答 • 2022-04-25 15:14:05

问题描述：

我有2个表类别，文件

类别表有id，bannerid，thumbid，

文件表有id，路径

i想要像

选择category.id，category.bannerid，category.thumbid，file.path在category.bannerid = file上的左连接文件.id as bannerimagepath

选择category.id，category.bannerid，category.thumbid，file.path左边的连接文件。 thumbid = file.id as thumbimagepath

我尝试过：

$ b $嗨我有2个表格文件

文件

id |路径

1 | c / image1.jpg

2 | c / image2.jpg

3 | c / image3.jpg

4 | c / image4.jpg

5 | c / image5.jpg

6 | c / image6.jpg

和第二个表格

bannerimages

id | bigimageid | thumbimageid

1 | 2 | 1

2 | 3 | 4

3 | 5 | 6

我必须找出像

id |横幅路径|拇指路径

1 | c / image2.jpg | c / image1.jpg

1 | c / image3.jpg | c / image4.jpg

1 | c / image5.jpg | c / image6.jpg

I have 2 tables category , file
category table have id, bannerid, thumbid,
file table has id, path

i want like

select category.id, category.bannerid, category.thumbid ,file.path left join file on category.bannerid = file.id as bannerimagepath

select category.id, category.bannerid, category.thumbid ,file.path left join file on category.thumbid = file.id as thumbimagepath

What I have tried:

hi I have 2 tables file
file
id | Path
1 | c/image1.jpg
2 | c/image2.jpg
3 | c/image3.jpg
4 | c/image4.jpg
5 | c/image5.jpg
6 | c/image6.jpg
and second tabel
bannerimages
id | bigimageid | thumbimageid
1 | 2 | 1
2 | 3 | 4
3 | 5 | 6
I have to find out like
id | banner path | thumb path
1 | c/image2.jpg | c/image1.jpg
1 | c/image3.jpg | c/image4.jpg
1 | c/image5.jpg | c/image6.jpg

答

您需要两次加入文件表。因此，您需要为表分配两个不同的别名，以便在引用它时没有歧义。

You need to join to the file table twice. For that reason you need to assign the table two different aliases so there is no ambiguity when referencing it.

select
	category.id,
	bannerfile.[path] as bannerimagepath,
	thumbfile.[path] as thumbimagepath
from 
	[category]
	left join [file] as bannerfile on category.bannerid = bannerfile.id
	left join [file] as thumbfile on category.thumbid = thumbfile.id

您可以使用以下查询

You can use following query

Select id,(Select path from file where A.bigimageid =id ) as bannerPath,
(Select path from file where A.thumbimageid =id ) as ThumbPath FROM Bannerimages A

假设如果bannerid或thumbid中的某个地方的横幅图像表包含空值

那么将如何编写查询

你能告诉我如何处理连接中的null

Suppose if banner image table if somewhere in bannerid or thumbid contain null value

then how will write query

Can you tell me how to deal with null in join