您如何在mysql的同一个表上两次连接? [英] How do you join on the same table, twice, in mysql?

问题描述

我有2张桌子.一个(域)具有域名ID和域名(dom_id，dom_url).

另一个包含实际数据，其中2列需要TO和FROM域名.因此，我有2列rev_dom_from和rev_dom_for，它们都存储了domains表中的域名ID.

简单.

现在，我需要在网页上同时显示两个域名.我知道如何通过在reviews.rev_dom_for = domains.dom_url查询中的LEFT JOIN域显示一个或另一个，然后回显dom_url，它会回显rev_dom_for列中的域名.

但是我如何使它在dom_rev_from列中回显第二个域名?

解决方案

您将使用另一个联接，类似于以下内容:

SELECT toD.dom_url AS ToURL, 
    fromD.dom_url AS FromUrl, 
    rvw.*

FROM reviews AS rvw

LEFT JOIN domain AS toD 
    ON toD.Dom_ID = rvw.rev_dom_for

LEFT JOIN domain AS fromD 
    ON fromD.Dom_ID = rvw.rev_dom_from

EDIT :

您要做的就是多次加入表中.查看帖子中的查询:它从Reviews表(别名为rvw)中选择值，该表为您提供了对Domain表的两个引用(FOR和FROM).

在这一点上，将域表加入评论表是一件简单的事情. FOR一次(别名为toD)，FROM再次(别名为fromD).

然后在SELECT列表中，您将从DOMAIN表的两个LEFT JOINS中选择DOM_URL字段，并通过表别名为每个引用到Domains表的已联接的表别名进行引用，并将它们作为ToURL和FromUrl进行别名. /p>

有关SQL中的别名的更多信息，请在此处阅读.

I have 2 tables. One (domains) has domain ids, and domain names (dom_id, dom_url).

the other contains actual data, 2 of which columns require a TO and FROM domain names. So I have 2 columns rev_dom_from and rev_dom_for, both of which store the domain name id, from the domains table.

Simple.

Now I need to actually display both domain names on the webpage. I know how to display one or the other, via the LEFT JOIN domains ON reviews.rev_dom_for = domains.dom_url query, and then you echo out the dom_url, which would echo out the domain name in the rev_dom_for column.

But how would I make it echo out the 2nd domain name, in the dom_rev_from column?

解决方案

you'd use another join, something along these lines:

SELECT toD.dom_url AS ToURL, 
    fromD.dom_url AS FromUrl, 
    rvw.*

FROM reviews AS rvw

LEFT JOIN domain AS toD 
    ON toD.Dom_ID = rvw.rev_dom_for

LEFT JOIN domain AS fromD 
    ON fromD.Dom_ID = rvw.rev_dom_from

EDIT:

All you're doing is joining in the table multiple times. Look at the query in the post: it selects the values from the Reviews tables (aliased as rvw), that table provides you 2 references to the Domain table (a FOR and a FROM).

At this point it's a simple matter to left join the Domain table to the Reviews table. Once (aliased as toD) for the FOR, and a second time (aliased as fromD) for the FROM.

Then in the SELECT list, you will select the DOM_URL fields from both LEFT JOINS of the DOMAIN table, referencing them by the table alias for each joined in reference to the Domains table, and alias them as the ToURL and FromUrl.

For more info about aliasing in SQL, read here.

这篇关于您如何在mysql的同一个表上两次连接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！