如何从id相关的两个表中获取单个记录 [英] How to get a single record from two tables related by id

问题描述

select JobAds.AdID, JobAds.Title, JobAds.Description, JobAds.Date, AdPhotos.PhotoName,
State.StateName, City.CityName, SubCategory.Name, JobAds.Date
from State,City,SellerDetails,JobAds, AdPhotos ,SubCategory
where State.StateID = SellerDetails.StateID
and City.CityID = SellerDetails.CityID
and JobAds.SellerID = SellerDetails.SellerID
and JobAds.AdID = AdPhotos.AdID
and JobAds.SubCategoryID = SubCategory.SubCategoryID
order by JobAds.Date desc

上述查询中的
，在AdP中照片表，我有专栏 -

AdID PhotoName

1 photo1.jpg

1 photo2.jpg

1 photo3.jpg

2 photo4.jpg

正如您所看到的AdID 1我有3张照片，但我只想在上面的查询中使用上面的AdID的第一张照片。



请帮我解决问题，否则我将失去工作

in above query,in AdPhotos table, i have columns--
AdID PhotoName
1 photo1.jpg
1 photo2.jpg
1 photo3.jpg
2 photo4.jpg
As you can see for AdID 1 i have 3 photos but i want only the first photo for an AdID using above in above query.

Please help me out quckly,otherwise i'll lose my job

推荐答案

你可以使用按ID分组，然后取每组中的第一项。检查以下答案

所以q1

[ ^ ]

so q2 [ ^ ]

You can use group by id and then take the first item in each group. Check below answers
so q1
[^]
so q2[^]

在order by子句之前添加：

Add this before the order by clause:

and AdPhotos.PhotoName in
(select top 1 PhotoName from AdPhotos where AdID = JobAds.AdID)

希望这有助于保住工作。祝你好运。

Hope that helps keep your job. Good luck.

你可以使用子查询

u can use sub query

(select top 1 PhotoName from AdPhoto Where AddId=JobAds.AdID) as PhotoName

你可以放置这个查询地点的AdPhotos.PhotoName



gud luck

you can place this query place of AdPhotos.PhotoName

gud luck

这篇关于如何从id相关的两个表中获取单个记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！