mysql join same table twice

Navigate: Previous Message• Next Message. Query Used To distinguish the column names from one another, aliases for the actual the table name are used, since both the tables have the same name. The following are the syntax of a SELF JOIN in MySQL: SELECT s1.col_name, s2.col_name. Written By. Here is the code and . The T-SQL query to accomplish our task appears in the box below. The result set will include all rows from both tables, where each row is the combination of the row in the first table with the row in the second table. Description: The problem was found when testing with RQG - 32 threads - derivate of the WL5004_* grammar heavy concurrent DML and DDL Result on mysql-5.5-runtime last pull 2010-10-11 + patches for avoiding Bug#57345 btr_pcur_store_position abort for load with concurrent lock/unlock tables (problem was already visible without bug#57345 patch) ----- frequent Query: UPDATE t1 AS A NATURAL JOIN t1 . Expected Report. Introduction to MySQL CROSS JOIN clause. Table name . The relationship between the two tables above is the "CustomerID" column. Accept Solution Reject Solution. Alias is mainly used for the column and table. 01:45 How to drop a MySQL database? . Introduction to MySQL CROSS JOIN clause. EDIT: All you're doing is joining in the table multiple times. To join tables, you use the cross join, inner join, left join, or right join clause. mysql> drop table if exists a; Query OK, 0 rows affected (0.13 sec) mysql> create table a(i int . The fix will not be simple. We have two tables: customer and city, with a common column named city_id. A join is a method of linking data between one ( self-join) or more tables based on values of the common column between the tables. LEFT JOIN tableb AS t2 ON t2.id = a.column3. ; Second, specify the table that will be joined with the main table, which appears in the INNER JOIN clause (t2, t3,…). modifiedby_user_id. FROM T1. In terms of SQL "duplicate" is repeated tuple (or value which actually is a tuple too) in a result. I have a simple select statement from one table that lists the same numeric column (TRANAMT) twice. Now, if you want to join them together to get the customers' respective city names, you can do so with a join like this: select customer.customer_id, customer.firstname, customer.lastname, customer.birthdate, customer.spouse_id, Then, we can create the following SQL statement (that . [ Ext for Developers : https://www.hows.tech/p/recommended.html ] How to MySQL : How do y. left join same table twice slow query. As the same quantity value exists in more than two records, a DISTINCT clause is used. Cross join. user_name. I need a query in CodeIgniter changed to do two left join's to the same table without the field names clashing. To join tables, you use the cross join, inner join, left join, or right join clause. Syntax: SELECT * FROM TABLE_A A. I think you don't. I mean such joining itself has no effect of creating duplicates. In general, if each table has n and m rows respectively, the result set will have nxm rows. But basically, you join the same table twice, but renaming it to something else, turning it into its own entity. 1) Using self join to query hierarchical data. The syntax of self-join is the same as the syntax of joining two different tables. You can use table aliases to refer to a table twice. error_reports table error_id - node_error_label_id - node_error_status_id nodes table node_id - node_label Which gets me the correct data. . The MySQL Right Outer join also called Right Join. Assuming MySQL decides to join the tables in the order shown in the query, the following pseudocode shows how MySQL might execute the query: . How to MySQL : How do you join on the same table, twice, in mysql? It does that by using SELECT DISTINCT and selecting the Hours (A) value and the Hours (B) value for each column for the unique ID. In line 12 we create an alias for the Employee table named sp that represents the salesperson's data which we show in line 6. Take the table name out of the first line to leave just the alias: UPDATE INS1. So it is optional for you to use the Outer . Let us take an example of the right join. If the number is positive (&amp;gt;0) I want it to show in the first reference of TRANAMT. And how can I solve this? you cannot join temp table to itself. [INNER] JOIN table_2. SQL Server self join examples. Notice that the "CustomerID" column in the "Orders" table refers to the "CustomerID" in the "Customers" table. I've tried queries with Select as, and am having trouble with how to go. PHP & MySQL Projects for $10 - $30. Just join the Users table twice, but you need to use a different alias each time you use the same table: SELECT Aliases allow you to reference the table name with an abbreviation. The following are the syntax of a SELF JOIN in MySQL: SELECT s1.col_name, s2.col_name. 02:00 How to create and use a MySQL database? This query handles the case in which one method (either A or B) is missing for an ID value. You have to join T1 to T2 twice. . INNER JOIN ps_stock_available sa ON ps.id_product=sa.id_product AND ps.id_shop=sa.id_shop . I can't get my head around the logic with this. Because you refer to the same table twice in the . . FROM tbl_NSP_Inspection INS1. JOIN T2 ON Fruit1 = Fruit_ID. about referencing the same column in the peopledb twice. We first join table 1 and table 2 which deliver a temporary table with combined data from table1 and table2, which is at that point joined to table3. The same precedence interpretation also applies to statements that mix the comma operator with INNER JOIN, CROSS JOIN, LEFT JOIN, and RIGHT JOIN, all of which have higher precedence than the comma operator.. A MySQL extension compared to the SQL:2003 standard is that MySQL permits you to qualify the common (coalesced) columns of NATURAL or USING joins, whereas the standard disallows that. Right join. You use self-join to create a result set that joins the rows with the other rows within the same table. ON INS1 . FROM a. SELECT * FROM table1 LEFT JOIN table2 ON table2.id = table1.id LEFT JOIN table3 ON table3.id = table1.id. A join refers to the same table multiple times. Using MySQl 56 as my database Im trying to join a table twice on another joined table but Im getting duplicate results rather than the row combined. Number the rows in the orders table so that the most recent order for each customer gets number 1. Unless I'm missing something, which is possible, I can't alias a table name that doesn't exist. Currently I'm left joining to 4 other tables but I need to link to one of them twice. Part Table. You might have a table of users, and a table of user_friends, linking one user to another. To get the whole organization structure, you can join the employees table to itself using the employeeNumber and reportsTo columns. Options: Reply• Quote. When trying to join the "Part" table twice, the "Log" table Quantity gets doubled. MySQL Right Outer Join is one of the Types, which is useful to return all the existing records (or rows) from the Right table, and match rows from the left table. , u1.lastname AS 'creator_lastname'. SQL self joins are used to compare values of a column with values of another column in the same table. Type '\c' to clear the buffer. As of MySQL 8.0.14, a derived table may be defined as a lateral derived table to specify that such references are permitted. Wim . Then, we can create the following SQL statement (that . The self-join is a special kind of joins that allow you to join a table to itself using either LEFT JOIN or INNER JOIN clause. WHERE s1.common_col_name = s2.common_col_name; Any rows in the constituent tables that did not have a matching counterpart in the other table are removed from the results. Inner Join Same Table Twice With Different Conditions - In that case you want to use a Left join instead of an inner join. Self-joins often produce rows that are "near" duplicatesthat is, rows that contain the same values but in different orders. Suppose you join two tables using the CROSS JOIN clause. Sorry maybe I'm not explaining this correctly. The only reaso. The problem is when trying to echo out the data with PHP I have to use the column number and not the label because I get two columns with the same name, node_label from the nodes1 and nodes2 aliased tables. Given the above table, I want to find a query that can return results similar to the last table. Here we apply INNER JOIN the table with itself. MySQL Right Join. The table employees stores not only employees data but also the organization structure data. At this point it's a simple matter to left join the Domain table to the Reviews table. Log Table. Often, you may want to count the number of duplicate values in a MySQL table. ON A. Common_COLUMN =B. Select column values in a specific order within rows to make rows with duplicate sets of values identical. The Temporary table simply holds integers with are then used to filter in the pattern of select * from table A where A.Field in (select Val from TempTable) Inner Join (Select * from Table B where B.Field in (select Val from . Note that it works well for a PROCEDURE. SELECT MySQL with 02 counts in same table with WHERE and GROUP BY in same Query. Almost right. The reportsto column is used to determine the manager id of an employee.. 1) MySQL self join using INNER JOIN clause. Because you cannot refer to the same table more than one in a query, you need to use a table alias to assign the . Problem with MySQL multiple join on same table with condition. I'll need to study Postgres's appoach and Antony's suggestions to fix it. Earlier one was able to deadlock two unions SELECT * FROM t1 UNION SELECT * FROM t2; and SELECT * FROM t2 UNION SELECT *t1; MySQL is deadlock free because it opens all tables at once, in the beginning of query (it was a bug that union did not do it). The materials table has the following columns:-Part_ID-Name-Manufacturer (linked with foreign key to manufacturers table)-Model_Number-Primary Vendor (linked with foreign key to vendors table)-Alternate Vendor (linked with foreign key to vendors table)-Cost I am trying to do a join so that I can see all columns in the . This is why I've had to LEFT JOIN the same table twice in the query to even get the information. Here, we use aliases name for tables because both the table name are the same. 0. Let's take some examples to understand how the self join works. Select only the most recent order for each customer by filtering the records with row numbers equal to 1. Hey guys, So I have three tables: materials, manufacturers and vendors. INNER JOIN tbl_NSP_Inspection INS2. The sites use copies of the same database so it must be something with Mysql settings. Solution 3. The code looks pretty clean because you named you Foreign Keys (Fruit1, Fruit2 and Fruit3) different than your Primary Keys (Fruit_ID). SQL self join is used to join or compare a table to itself. Posted on January 5, 2018 at 2:04pm. I want to use that 'fieldvalue' column twice and name it differently based on the fieldid. mysql left-join self-join. And it cannot open temporary table twice - e.g. Please . Instead of joining two different tables, you join one table to itself. The relationship between the two tables above is the "CustomerID" column. WHERE s1.common_col_name = s2.common_col_name; Here's an alternative solution: SELECT c.name , sum (case when type = 'dog' then quant end) AS dogs , sum (case when type = 'cat' then quant end) AS cats FROM customers AS c JOIN purchases p ON c.id=p.owner_id GROUP BY c.name; Share. Code language: SQL (Structured Query Language) (sql) In this syntax: First, specify the main table that appears in the FROM clause (t1). ON table_1.id = table_2.table_1_id; An inner join is the most restrictive type of join because it only displays rows created by combining rows from each table. You use INSERT INTO <select-statement>; For example, for duplicating the data in a table (if there are no indexes preventing this), you can use INSERT INTO mydata SELECT * FROM mydata; The SEL. 14.6.2 Solution. Here I'm trying to generate a report based on log and part table. The Question is how can I do this in MySQL which refuses to allow using a Temporary table twice in the same Query! SET INS1.IsLastOfDay = 0. RaminHashimzadeh wrote: yes, both query will produce same result. Something like: SELECT a.id, b.code, COUNT (c.keys) as keycount. Subject. But what? Please . ---- Ramin Hashimzade with. Article; 1 how can reslove : InvalidArgumentError: Graph execution error? The hierarchy_table has 5 columns that all reference the name_table, so you need 5 joins.It may be better to use LEFT joins instead of INNER, in case some of these columns are nullable and you still want the rows returned:. Right join. I have a table with an ID and a value. All the Unmatched rows from the left table are filled with NULL Values. MySQL Alias is used when we are willing to provide an alternate name for the column or table. INNER JOIN b as b2 ON a.id = b2.id. The table is not actually copied, but SQL performs the command as though it were. The syntax of self-join is the same as the syntax of joining two different tables. In general, if each table has n and m rows respectively, the result set will have nxm rows. 02:10 How to create a tab. I've been looking into CASE, JOIN, and UNION but haven't had any luck. F. The syntax of the command for joining a table to itself is almost same as that for joining two different tables. The same precedence interpretation also applies to statements that mix the comma operator with INNER JOIN, CROSS JOIN, LEFT JOIN, and RIGHT JOIN, all of which have higher precedence than the comma operator.. A MySQL extension compared to the SQL:2003 standard is that MySQL permits you to qualify the common (coalesced) columns of NATURAL or USING joins, whereas the standard disallows that. A derived table cannot normally refer to (depend on) columns of preceding tables in the same FROM clause. Vol. c) RIGHT JOIN: Right Join gets all the rows from the Right table and common rows of both tables. In the single query listed below we will join the Sales table three times to the Employee table as shown in lines 12-14. If you use different aliases, you get "Can't open temp table twice". The table employees has two roles: one is the Manager . The result set will include all rows from both tables, where each row is the combination of the row in the first table with the row in the second table. Table is specified twice. You'll notice I didn't use "AS" in some places, also. Problem with MySQL multiple join on same table with condition. Because of this, SELECT DISTINCT will not eliminate the duplicates. where the last two user names is the name from the USERS table matching the user_id from the PAGES table. Nonlateral derived tables are specified using the syntax discussed in Section 13.2.11.8, "Derived Tables". . SELECT * FROM tablea AS a. FROM table1 s1, table1 s2. A join is a method of linking data between one ( self-join) or more tables based on values of the common column between the tables. There are many a occasions when you need to find duplicate values available in a column of a MySql table. Common_COLUMN. First, I would strongly recommend making your table aliases more meaningful. I'm just 3 months of experience in MySQL. The table aliases t1 and t2 are used to assign the T table different names in the query. You can start from the users table, join to user_friends, then join to user again with a . The same precedence interpretation also applies to statements that mix the comma operator with INNER JOIN, CROSS JOIN, LEFT JOIN, and RIGHT JOIN, all of which have higher precedence than the comma operator.. A MySQL extension compared to the SQL:2003 standard is that MySQL permits you to qualify the common (coalesced) columns of NATURAL or USING joins, whereas the standard disallows that. MySQL Query from one table - Select same field twice . To construct a self join, you select from the same table twice by using the SELECT statement with an inner join or outer join clause. MySQL supports the following types of joins: Inner join. The same precedence interpretation also applies to statements that mix the comma operator with INNER JOIN, CROSS JOIN, LEFT JOIN, and RIGHT JOIN, all of which have higher precedence than the comma operator.. A MySQL extension compared to the SQL:2003 standard is that MySQL permits you to qualify the common (coalesced) columns of NATURAL or USING joins, whereas the standard disallows that. 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). I need to query the same table and column name two different times in the query to get the data. Join the customers table with the table containing only the most recent orders. Any help appreciatted. Table name . You can use this technique regardless of how many other tables are needed for the query and whether you need to join the table to itself or onto other places. Temporarily assigning a name for column or table that is user friendly and understandable. Left join. Consider the following staffs table from the sample database: SELECT o.name AS object, p1.name AS parent_1, p2.name AS parent_2, c1.name AS child_1, c2.name AS child_2 FROM hierarchy_table AS h LEFT JOIN name_table AS o ON h.object_id . What the heck is a1_ and a2_?. How can I make a selection that gives the following result. Using MySQl 56 as my database Im trying to join a table twice on another joined table but Im getting duplicate results rather than the row combined. A JOIN clause is used to combine rows from two or more tables, based on a related column between them. Studio Report Query - Joining to same table twice. ; Third, specify a join condition after the ON keyword of the INNER JOIN clause. Inner Join Same Table Twice With Different Conditions - In that case you want to use a Left join instead of an inner join. MySQL supports the following types of joins: Inner join. Also, since you state, "Importantly, some "Course" records have no corresponding log.resumeComplete record in the Log table . Posted. If the number is negative (&amp;lt;0) I want it to should in the next reference of TRANAMT. The syntax of the command for joining a table to itself is almost same as that for joining two different tables. If a value is missing for either A or B, then the result is NULL. Notice that the "CustomerID" column in the "Orders" table refers to the "CustomerID" in the "Customers" table. FROM table1 s1, table1 s2. Answer (1 of 4): It doesn't matter if the data comes from the same table or a different one. The same precedence interpretation also applies to statements that mix the comma operator with INNER JOIN, CROSS JOIN, LEFT JOIN, and RIGHT JOIN, all of which have higher precedence than the comma operator.. A MySQL extension compared to the SQL:2003 standard is that MySQL permits you to qualify the common (coalesced) columns of NATURAL or USING joins, whereas the standard disallows that. INNER JOIN b on a.id = b.id. LINQ group by query help: Select another related table with key. Improve this answer. A JOIN clause is used to combine rows from two or more tables, based on a related column between them. Procedure call is not treated as "same query"? Then you add the phone "type" in there to force it to only pick up that certain row, since the left join without it would grab all three phone types. pageid, page_name, body, user_name, user_name. Cross join. drop table if exists a; create table a(i int); insert . This is what I mean: SELECT request.ID, users.Name, /*name for Createdby */ users.Name, /*name for Updatedby */ requests.DateCreated, requests.DateUpdated FROM requests LEFT JOIN users ON requests.Updatedby = users.ID. You get a "multiplier effect" since you are joining the purchase table twice. The query references the table T twice. LEFT JOIN TABLE_B B. Use the same column twice in a SELECT. LEFT JOIN tableb AS t1 ON t1.id = a.column2. It might make things clearer to either set up the relevant subsets of your log data as a subquery or CTE, or specify the date criteria in your JOIN clause. Rails ActiveRecord joins the same table twice - MySQL [ Glasses to protect eyes while coding : https://amzn.to/3N1ISWI ] Rails ActiveRecord joins the same t. table_1. Here, we use aliases name for tables because both the table name are the same. The join condition specifies the rule for matching rows . Kindly let me know where I'm doing wrong. the same table twice but in different ways. Final status: the bug is still assigned to me. Answer (1 of 4): OQ: How do you get duplicate values when joining the same table twice in T-SQL? I'm trying to build a sql query that joins table a to table b twice. Joining for the second fruit is a little more difficult. 2 Left join. /Michael. . For example, to find any two customers with the The two-pass algorithm can be quite expensive, because it reads the rows from the table twice, and the second read causes a lot of random I/O. 00:15 How to start MySQL from the command prompt? [USERS] user_id. To distinguish the column names from one another, aliases for the actual the table name are used, since both the tables have the same name. Again, we can implement the above strategy using CTEs: Column Alias are used to give alternate name for the column headings which are easy for the end- user to understand. Suppose you join two tables using the CROSS JOIN clause. SubQueries SQL Three Table Join Examples Lesson 7: Advanced MySQL Concepts - UniversalClass MySQL Course Database Design Course - Learn how to design and plan a database for beginners How . This equation can be extended to more than 3 tables to N tables, You just need to make . customers as (select 1 c_no,'Name_1' c_name,'City_1' city from dual union all TOP Ranking. The table is not actually copied, but SQL performs the command as though it were. Box below clause is used to give alternate name for column or table that is user and! Create and use a MySQL database following result b.code mysql join same table twice count ( c.keys ) as keycount use to. Cross join, left join, INNER join clause on the fieldid t1 and are! Works with examples - EDUCBA < /a > error_reports table error_id - -! To make rows with the table name out of the first line to leave the. Be extended to more than two records, a derived table may be defined as a lateral table! Here we apply INNER join with same table with condition in same table multiple times than tables! U1.Lastname as & quot ; CustomerID & quot ; CustomerID & quot ; column to the same quot! ( c.keys ) as keycount referencing the same quantity value exists in more than 3 tables to n,... This query handles the case in which one method ( either a or B, then join the. You use the CROSS join, INNER join to 4 other tables but I need query! The T-SQL query to get the whole organization structure, you can from! The manager ID of an employee.. 1 ) using self join works the single query below... Because you refer to the same column in the to specify that such are. Make a selection that gives the following SQL statement ( that > error_reports error_id. Query hierarchical data table node_id - node_label which gets me the correct data query hierarchical data clause is used give... The two tables using the syntax of a column with values of another column in the tables! Shown in lines 12-14 counts in same table table three times to the last two user names the! Know where I & # x27 ; m doing wrong body, user_name, user_name,,... Using the employeeNumber and reportsto columns easy for the column headings which are for. To left join tableb as t2 on t2.id = a.column3 ; derived tables & quot ; name for column! Easy for the column and table itself using the syntax of the INNER join, left join tableb as on... A simple matter to left join tableb as t1 on t1.id = a.column2 ( either a or B, join... Is user friendly and understandable each customer by filtering the records with row numbers equal 1. Joining two different tables rows to make rows with the other rows the. T-Sql query to get mysql join same table twice data a value the box below you join two above.: https: //www.hows.tech/p/recommended.html ] How to join to query the same the single query below. Some examples to understand How the self join works tables using the CROSS clause...: //www.oreilly.com/library/view/high-performance-mysql/9780596101718/ch04.html '' > Gude to How does MySQL alias works with mysql join same table twice - EDUCBA < /a > T-SQL... Null values = a.column2 can not open temporary table twice this, DISTINCT! Procedure call is not treated as & quot ; CustomerID & quot ; CustomerID & quot.! Results similar to the same table and column name two different times in the query to the... Currently I & # x27 ; m not explaining this correctly sites use of! This query handles the case in which one method ( either a or B, then the result set joins... Manager ID of an employee.. 1 ) MySQL self join in MySQL: How do.! Determine the manager ID of an employee.. 1 ) using self join using INNER join with same table condition. Correct data the sites use copies of the Right join < /a > Introduction to MySQL: another! Similar to the same table and column name two different tables, you one! Linq group by in same table twice the table containing only the most recent order each! Structure, you just need to query the same values of a self join to,. Values identical rows within the same column in the constituent tables that did not have a table -! Is almost same as that for joining a table to specify that such references are permitted the. > UPDATE on table to itself is almost same as that for joining two different in! Our task appears in the peopledb twice different tables a result set will have nxm rows the records row! Clause is used: //www.sqlservercentral.com/forums/topic/update-on-table-to-inner-join-with-same-table '' > postgresql - How to join,! Is optional for you to use that & # x27 ; creator_lastname & # x27 m! Referencing the same column in the peopledb twice derived table may be defined as lateral! And reportsto columns other table are removed from the users table matching the user_id from the results the.. Join with same table twice are used to determine the manager with where and by. And t2 are used to determine the manager ID of an employee.. 1 ) using self join in:! Has no effect of creating duplicates with row numbers equal to 1 be something MySQL... ; same query in lines 12-14 and it can not open temporary table twice be something with MySQL multiple on... Column twice and name it differently based on the fieldid leave just the alias: UPDATE INS1 method either! Doing wrong the t table different names in the table with condition take the table are. Shown in lines 12-14 the single query listed below we will join the customers table with condition can from! Make rows with the other table are filled with NULL values fieldvalue & x27! And common rows of both tables example of the first line to leave just the alias: INS1. Sites use copies of the Right join extended to more than 3 to... Select column values in a specific order within rows to make for matching rows the join condition after on. This query handles the case in which one method ( either a or )! Select same field twice '' > postgresql - How to join to query the same table times... Update on table to INNER join clause Third, specify a join after. Join one table to specify that such references are permitted between the two tables above is the quot... To refer to a table to the same column in the query to get the data no effect of duplicates! Eliminate the duplicates the data a result set will have nxm rows if a value is missing for ID. If exists a ; create table a ( I int ) ; insert t2 on =! I mean such joining itself has no effect of creating duplicates are removed from the PAGES.!, count ( c.keys ) as keycount - node_error_label_id - node_error_status_id nodes table node_id - which... Sets of values identical 1 How can reslove: InvalidArgumentError: Graph execution error manager ID of employee. Tables using the CROSS join, left join, or Right join some examples to.... Update on table to INNER join the Domain table to itself using the and! Itself has no effect of creating duplicates refer to a table to is... That is user friendly and understandable counterpart in the, a DISTINCT clause used! Will join the customers table with the table name out of the same column in the other rows the. Table employees has two roles: one is the & mysql join same table twice ; tables... A to table B twice using INNER join, or Right join gets all the rows with duplicate sets values... Join also called Right join: Right join clause any rows in the twice! Create the following types of joins: INNER join clause, s2.col_name references are permitted so. Joining for the end- user to another count the number of duplicate values a. Of a self join using INNER join B as b2 on a.id = b2.id u1.lastname as & quot ; twice! Int ) ; insert table are filled with NULL values MySQL CROSS join clause left join INNER! Is almost same as that for joining a table with the other rows within same. How do y body, user_name, user_name for the mysql join same table twice user to.. Using INNER join let me know where I & # x27 ; column twice and it! Same table and common rows of both tables to itself using the syntax of a self join mysql join same table twice:... Case in which one method ( either a or B, then the result set will nxm... M doing wrong ID and a table of users, and a.. Count ( c.keys ) as keycount a DISTINCT clause is used to compare values of another column in single. To create a result set that joins the rows with duplicate sets of values.... Line to leave just the alias: UPDATE INS1 join on same table and column name two times. Join with same table peopledb twice MySQL Right Outer join also called Right join Domain table to the database... Tableb as t2 on t2.id = a.column3 where I & # x27 ; column a... On log and part table task appears in the box below know where I & # x27 ; user_name user_name. Pageid, page_name, body, user_name, left join, left join tableb as t2 on =... Select column values in a specific order within rows to make, a! Are specified using the CROSS join, INNER join B as b2 on a.id = b2.id friendly. Know where I & # x27 ; m not explaining this correctly us take an example of the table! T2.Id = a.column3 > UPDATE on table to the same table How the self join in MySQL: do... To user_friends, then join to user_friends, then join to user_friends, linking one user to another numbers. Re doing is joining in the to MySQL CROSS join clause because both the table aliases t1 and are.