How do you join on the same table twice in mysql

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · 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   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

User · 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   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

User · 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   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

User · Answer

Read this and try  this will help you   Table1   column11 column12 column13 column14   Table2   column21 column22 column23 column24   SELECT table1 column11 table1 column12 table2asnew1 column21 table2asnew2 column21  FROM table1 INNER JOIN table2 AS table2asnew1 ON table1 column11 table2asnew1 column21  INNER TABLE table2 as table2asnew2 ON table1 column12 table2asnew2 column22   table2asnew1 is an instance of table 2 which is matched by table1 column11 table2asnew1 column21  and   table2asnew2 is another instance of table 2 which is matched by table1 column12 table2asnew2 column22

User · Answer

Read this and try  this will help you   Table1   column11 column12 column13 column14   Table2   column21 column22 column23 column24   SELECT table1 column11 table1 column12 table2asnew1 column21 table2asnew2 column21  FROM table1 INNER JOIN table2 AS table2asnew1 ON table1 column11 table2asnew1 column21  INNER TABLE table2 as table2asnew2 ON table1 column12 table2asnew2 column22   table2asnew1 is an instance of table 2 which is matched by table1 column11 table2asnew1 column21  and   table2asnew2 is another instance of table 2 which is matched by table1 column12 table2asnew2 column22

User · 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   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

User · Answer

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

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

Examples related to mysql