如何在2个表中选择具有不同连接条件的一列 [英] How to select one column with different join condition in 2 tables

问题描述

我有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

这篇关于如何在2个表中选择具有不同连接条件的一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！