How to Join to a self Joined table?-CodePudding

I have a table where I have performed a self join:

SELECT A.number AS number1,
B.number AS number2 ,
A.addres_line1,
A.address_line2 
FROM addresses A, addresses B
WHERE (CONCAT(A.addres_line1, A.addres_line1) = CONCAT(B.addres_line1, B.addres_line1) AND A.number <> B.number)
GROUP BY CONCAT(A.addres_line1, A.addres_line1)
;

But I need to Join other tables to this so I may narrow my query down further. How would I be able to do this?

CodePudding user response：

Firstly, there is nothing special about a self-join as far as SQL is concerned - you can mention the same table 99 times if you need to, in just the same way you'd join 99 different tables.

Secondly, you are using an extremely out-dated form of SQL, where tables were listed with commas. Modern SQL uses the "JOIN" keyword, which leads to much clearer queries, particularly when extended to variants such as "LEFT JOIN".

In modern SQL, with a bit of indenting for readability, and fixing some typos, your query would be:

SELECT
   A.number AS number1,
   B.number AS number2,
   A.address_line1,
   A.address_line2 
FROM
   addresses A
JOIN
   addresses B
   ON CONCAT(A.address_line1, A.address_line2) = CONCAT(B.address_line1, B.address_line2)
   AND A.number <> B.number
GROUP BY
   -- Note: This group by doesn't match the select list, so won't work
   CONCAT(A.address_line1, A.address_line2)

To join other tables, you simply add more JOIN clauses, each with their own ON clause telling the DBMS how it relates to the rest of the query:

SELECT
   A.number AS number1,
   B.number AS number2,
   A.address_line1,
   A.address_line2 
FROM
   addresses A
JOIN
   addresses B
   ON CONCAT(A.address_line1, A.address_line2) = CONCAT(B.address_line1, B.address_line2)
   AND A.number <> B.number
JOIN
   some_other_table C
   ON C.some_column = A.some_column
GROUP BY
   -- Note: This group by doesn't match the select list, so won't work
   CONCAT(A.address_line1, A.address_line2)

CodePudding user response：

First thing to do is rewrite the query to (not using old-style joins):

SELECT A.number AS number1,
B.number AS number2 ,
A.addres_line1,
A.address_line2 
FROM addresses A
INNER JOIN addresses B ON (CONCAT(A.addres_line1, A.addres_line1) = 
                         CONCAT(B.addres_line1, B.addres_line1) 
                     AND A.number <> B.number)
GROUP BY CONCAT(A.addres_line1, A.addres_line1)
;

After this adding a join is done like this:

SELECT A.number AS number1,
B.number AS number2 ,
A.addres_line1,
A.address_line2 
FROM addresses A
INNER JOIN addresses B ON (CONCAT(A.addres_line1, A.addres_line1) = 
                         CONCAT(B.addres_line1, B.addres_line1) 
                     AND A.number <> B.number)
INNER JOIN otherTable C ON C.someField = A.someOtheField
GROUP BY CONCAT(A.addres_line1, A.addres_line1)
;

In this example I used INNER JOIN. Of course it is possible to use LEFT JOIN if that's needed for your data.