MySql. How to use Self Join

by

You’re so close!

Since you say you’re displaying the country and year from A and limiting by A. Country of Turkey, Turkey is all you’re going to see. You either need to change the selects to be B.country and B.year or change the where clause to be B.country.

This is using a cross join which will get slower the more records there are in a table.

SELECT DISTINCT b.Country, b.Year 
FROM table1 AS a, 
     table1 AS b 
WHERE a.Year=b.Year 
  and a.Country='Turkey';

could be written as… and would likely have the same execution plan.

SELECT DISTINCT b.Country, b.Year 
FROM table1 AS a 
CROSS JOIN table1 AS b 
WHERE a.Year=b.Year 
  and a.Country='Turkey';

OR
This uses an INNER JOIN which limits the work the engine must do and doesn’t suffer from performance degradation that a cross join would.

SELECT DISTINCT a.Country, a.Year 
FROM table1 AS a 
INNER JOIN table1 AS b 
   on a.Year=b.Year 
  and b.Country='Turkey';

WHY:

Consider what the SQL engine will do when the join occurs
A B

+------------+------+--------+------------+------+--------+
| A.Country  | Rank |  Year  | B.Country  | Rank |  Year  |
+------------+------+--------+------------+------+--------+
|France      |  55  |  2000  |France      |  55  |  2000  |
+------------+------+--------+------------+------+--------+
|Canada      |  30  |  2000  |France      |  55  |  2000  |
+------------+------+--------+------------+------+--------+ 
|Turkey      |  78  |  2000  |France      |  55  |  2000  |
+------------+------+--------+------------+------+--------+ 
|France      |  55  |  2000  |Canada      |  30  |  2000  |
+------------+------+--------+------------+------+--------+
|Canada      |  30  |  2000  |Canada      |  30  |  2000  |
+------------+------+--------+------------+------+--------+ 
|Turkey      |  78  |  2000  |Canada      |  30  |  2000  |
+------------+------+--------+------------+------+--------+ 
|France      |  55  |  2000  |Turkey      |  78  |  2000  |
+------------+------+--------+------------+------+--------+
|Canada      |  30  |  2000  |Turkey      |  78  |  2000  |
+------------+------+--------+------------+------+--------+ 
|Turkey      |  78  |  2000  |Turkey      |  78  |  2000  |
+------------+------+--------+------------+------+--------+

So when you said display A.Country and A.Year where A.Country is Turkey, you can see all it can return is Turkey (due to the distinct only 1 record)

But if you do B.Country is Turkey and display A.Country, you’ll get France, Canada and Turkey!

More Related Contents:

  1. get first occurrence of last changed value of a column [closed]
  2. SQL Print the name of all Employees together with the name of their supervisor
  3. MySQL – Rows to Columns
  4. ROW_NUMBER() in MySQL
  5. Left Outer Join doesn’t return all rows from my left table?
  6. How can I temporarily disable a foreign key constraint in MySQL?
  7. MySQL offset infinite rows
  8. Ordering by the order of values in a SQL IN() clause
  9. Creating a “Numbers Table” in MySQL
  10. SQL Selecting from two Tables with inner join and limit [duplicate]
  11. Can a foreign key reference a non-unique index?
  12. MySQL Group by SUM
  13. Get the latest date from grouped MySQL data
  14. In SQL, how to select the top 2 rows for each group
  15. Mysql query to dynamically convert rows to columns on the basis of two columns
  16. MySQL Error 1264: out of range value for column
  17. How to change the default port of mysql from 3306 to 3360
  18. Special Characters in MySQL Table Name
  19. Is it possible to reference one column as multiple foreign keys?
  20. MySQL: How do I join same table multiple times?
  21. #1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’
  22. How can we find gaps in sequential numbering in MySQL?
  23. Empty IN clause parameter list in MySQL
  24. creating a random number using MYSQL
  25. #1292 – Incorrect date value: ‘0000-00-00’ [duplicate]
  26. How to speed up sql queries ? Indexes?
  27. mysql select id and name from other table and join query
  28. SQL query with avg and group by
  29. Optimistic locking in MySQL
  30. Insert and set value with max()+1 problems

Leave a Comment