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

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.

Given the following tables..

Domain Table
dom_id | dom_url

Review Table
rev_id | rev_dom_from | rev_dom_for

Try this sql... (It's pretty much the same thing that Stephen Wrighton wrote above) The trick is that you are basically selecting from the domain table twice in the same query and joining the results.

Select d1.dom_url, d2.dom_id from
review r, domain d1, domain d2
where d1.dom_id = r.rev_dom_from
and d2.dom_id = r.rev_dom_for

If you are still stuck, please be more specific with exactly it is that you don't understand.

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

Related

Recent Posts