SQL beginners can have some problems with understanding how Self-Join works. But everybody should understand
how handy this query type is. It helps us to combine two records inside same table into single row.
This thing is widely used on some level of programming and for sure we all sure understand, how to use it.
The first, very optimistic thing is that Self-Join basically have a structure of typical JOIN syntax with some small improvements.
Let's say we have some invoices table.
| id | name | company_from | company_to | proforma_id |
| 1 | INVOICE-1 | Company A | Company D | |
| 2 | INVOICE-2 | Company B | Company C | |
| 3 | PROFORMA-1 | Company A | Company D | 12 |
As you can see, one of invoices has a proforma_id. Proforma is just a preliminary document bill,
when you client will pay money, proforma gets a main invoice.
Let's say we want to get a list of invoices and their proformas.
SELECT invoice.name, proforma.name
FROM invoices AS invoice
JOINS invoices AS proforma ON
invoice.proforma_id = proforma.id The code above will treat one table as two seperate tables with different aliases.
We just have created a reference from invoices table to itself.
The result of this query should be:
| invoice.name | proforma.name |
| INVOICE-1 | PROFORMA-1 |
And that's it! The syntax here is quite simple, it is important to just understand how it works.