I do not know much about SQL. I work in a factory that manufactures microchips. We manufacture these microchips in batches of 5,000 microchips at a time. Each batch of microchips is primarily identified by a 'Lot Code'.
Lot codes are always a string that begins with '1000-' and are followed by 6 natural numbers. So, '1000459347' for example, is a lot code.
The first stage of manufacturing the microchips involves two batches of microchips being run together (in parallel). Theses 2 batches are called 'Sister Lots'. So, for example, the sister lot for '1000459347' was lot '1000459347'.
There is a table called EM_MACHINE_LG that displays the sister lots in adjacent columns.
| LOT1 | LOT2 |
|---|---|
| 1000459347 | 1000459348 |
(This may not be relevant - but note that sister lot codes do not always differ by +1, nor is LOT1 always the smaller number, nor is it always an odd number.)
There is another table called MACHINE_LOT, that displays a different identifier for the lots known as 'LOT IDs'.
It displays entries like this:
| LOT_CODE | LOT_ID |
|---|---|
| 1000459347 | 1712371801 |
| 1000459348 | 1722371811 |
Is it possible to join these two tables together, such that I obtain a table like this?
| LOT_CODE1 | LOT_ID1 | LOT_CODE2 | LOT_ID2 |
|---|---|---|---|
| 1000459347 | 1712371801 | 1000459348 | 1722371811 |
You can join the same table twice, but you need unique aliases.
The example below should work.
SELECT
EM_MACHINE_LG.LOT1 AS LOT_CODE1
SL1.LOT_ID AS LOT_ID1
EM_MACHINE_LG.LOT2 AS LOT_CODE2
SL2.LOT_ID AS LOT_ID2
FROM EM_MACHINE_LG
JOIN MACHINE_LOT SL1 ON EM_MACHINE_LG.LOT1 = SL1.LOT_CODE
JOIN MACHINE_LOT SL2 ON EM_MACHINE_LG.LOT2 = SL2.LOT_CODE