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

问题描述

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

推荐答案

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

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

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