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

by

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.

More Related Contents:

  1. How to delete duplicates on a MySQL table?
  2. need to return two sets of data with two different where clauses
  3. Select last row in MySQL
  4. Storing image in database directly or as base64 data?
  5. Implementation of Levenshtein distance for mysql/fuzzy search?
  6. Is MySQL breaking the standard by allowing selecting columns that are not part of the group by clause?
  7. MySQL – Operand should contain 1 column(s)
  8. Restore table structure from frm and ibd files
  9. Can a number be used to name a MySQL table column?
  10. Mysql: Setup the format of DATETIME to ‘DD-MM-YYYY HH:MM:SS’ when creating a table
  11. mysql pivot/crosstab query
  12. MySQL: Split comma separated list into multiple rows
  13. UNION query with codeigniter’s active record pattern
  14. How to start MySQL with –skip-grant-tables?
  15. When to use MongoDB or other document oriented database systems? [closed]
  16. Remove Primary Key in MySQL
  17. Difference in MySQL JOIN vs LEFT JOIN
  18. Update a column in MySQL
  19. mysql stored procedure that calls itself recursively
  20. MySql views performance [closed]
  21. When to use NULL in MySQL tables
  22. What is the difference between tinyint, smallint, mediumint, bigint and int in MySQL?
  23. Invalid default value for ‘create_date’ timestamp field
  24. Detecting locked tables (locked by LOCK TABLE)
  25. How to get size of mysql database?
  26. MySQL CURRENT_TIMESTAMP on create and on update
  27. Calculating time difference between 2 dates in minutes
  28. Access denied after setting user’s password with SHA256 in phpMyAdmin
  29. connecting MySQL server to NetBeans
  30. Join two tables (with a 1-M relationship) where the second table needs to be ‘flattened’ into one row

Leave a Comment