如何加入两个表并获得正确的记录 [英] How to join two table and get the correct records

问题描述

我有一个包含页面/广告的表格和另一个包含每个广告 x 张图片的表格.图片表还有一个sortorder"列.

I have one table containing pages/ad and another table with x pictures for each ad. The pictures table also have a "sortorder" column.

我正在尝试编写一些 SQL，其中我从广告中获取标题，并且只有一张图片的排序顺序最低.

I’m trying to write some SQL, where i get the title from an ad and only one picture where sortorder is the lowest.

到目前为止，我正在这样做.但这只是给了我一张照片，而不是我想要的照片.

So far i’m doing this. But that just gave me a picture, but not the picture i want.

SELECT ads.id, ads.title, min(ads_gallery_files.filename) as picture, clients.name
FROM ads LEFT JOIN ads_gallery_files ON ads_gallery_files.ads_id=ads.id 
GROUP BY ads.id

我还有一个客户表，我可以在其中查看谁创建了广告.我正在使用左连接来收集该名称

I also have a client table, where i can see who created the ad. I’m using a left join to collect that name

LEFT JOIN clients ON clients.id=ads.client_id

也许我必须在 SELECT 中做一个 SELECT，不知道.

Maybe i have to do a SELECT in a SELECT, don’t know.

推荐答案

你需要一个表表达式(我命名为 m)来计算最小 sortorder每个广告.然后你可以加入这个表来过滤掉你不想要的额外"行.

You'll need a table expression (that I named m) to compute the minimum sortorder for each ad. Then you can just join this table to filter out the "extra" rows you don't want.

例如:

select
   a.id, a.title, f.filename as picture, c.name
from ads a
join ads_gallery_files f on f.ads_id = a.id
join ( -- this is called a "table expression" since it acts as a table
  select ads_id, min(sortorder) as minorder
  from ads_gallery_files f
  group by ads_id
) m on m.ads_id = f.ads_id and m.minorder = f.sortorder
left join clients c on c.id = a.client_id

这篇关于如何加入两个表并获得正确的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！