How do you join on the same table, twice, in mysql?

mysql

user15063 · Oct 14, 2008 · Viewed 112.7k times · Source

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?

Answer

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:

How do you join on the same table, twice, in mysql?

Answer

Related questions