帮助抓取 (INNER?) JOIN [英] Help with grasping (INNER?) JOIN

问题描述

我在构建查询时遇到问题.我可以在 3 个不同的查询中做我想做的事情.

I'm having trouble building a query. I can do what I want in 3 different queries.

SELECT id FROM table1 WHERE url LIKE '%/$downloadfile'
put that in $url_id
SELECT item_id FROM table2 WHERE rel_id = '$url_id'"
put that in $item_id
SELECT rel_id FROM table2 WHERE rel_id = '$item_id' AND field_id = '42'"
put that in $user_id

但是通过阅读有关连接和内部连接的示例，我认为有一种更优雅的方式.我无法围绕编写更好的查询(但想)我可以描述它应该如何进行:

But from reading examples on joins and inner joins I think there's a more elegant way. I cant wrap my brain around writing a better query (but would like to) I can describe how it should go:

表1字段:id、url

table1 fields: id, url

表2字段 item_id、rel_id、field_id

table2 fields item_id, rel_id, field_id

我知道 table1.url 的最后一部分(LIKE '%/$filename')，我选择了 table1.id.table1.id 等于 table2.rel_id 中的一个条目.所以得到它并选择table2.item_id.在 table2 中有另一个条目具有相同的 table2.item_id 并且它将有一个 table2.field_id = '42'最后，我需要的值是 table2.rel_id，其中 table2.field_id 为 42.我将获取该值并将其放入 $user_id

I know the last part of table1.url (LIKE '%/$filename') with that I select table1.id. table1.id is equal to one entry in table2.rel_id. So get that and select the table2.item_id. In table2 there is another entry which has the same table2.item_id and it will have a table2.field_id = '42' And finally the value I need is the table2.rel_id where the table2.field_id was 42. I will fetch that value and put it in $user_id

这是否可以通过使用连接/内部连接的一个查询来完成?

Can this be done with one query using joins/inner joins?

推荐答案

SELECT url, second.rel_id AS user_id
FROM table1
INNER JOIN table2 AS first
  ON table1.id=first.rel_id
INNER JOIN table2 AS second
  ON first.item_id=second.rel_id
WHERE second.field_id='42'
  AND table1.url LIKE '%/:downloadfile'

这篇关于帮助抓取 (INNER?) JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！