Is there a way to do a SELF JOIN on on the same column?

Question

I currently have an inelegant solution that requires iterating through thousands of rows one at a time and I would like to know if it's possible to do this with a single SQL statement.

I have a database table called history that holds a record of all transactions on another database called inventory. Both databases share a column called pKey (the foreign key).

My Inventory database:

| ID | pKey | model | room | ip | active | ... |

My history database: The pKey is the foreign key

| ID | pKey | fieldName | oldValue | newValue |

In the history database there can be more than 20 transactions for a single pKey. I would like to find all rows in the history that have:

1. fieldName of A AND oldValue of B
2. fieldName of C AND oldValue of D
3. the same pKey.

I.e. say we find that we have a row in the history table with fieldName of A and old Value of B, the result will only be valid if the pKey associated with that row also turns up in the search for rows with a fieldName of C and oldValue of D.

After doing some research it seems that SELF JOIN would be a good bet, but I am getting errors since I'm trying to do an SELF JOIN on the same column. Here is my statement:

SELECT pKey FROM `history` INNER JOIN history ON history.pKey=history.pKey WHERE `fieldName` = 'ipAddress' AND `oldValue` LIKE '129.97%'

EDIT: I made a mistake writing my statement here; I only mean to select the pKey results.

Answer 1

When you need to perform SELF-JOIN, you have to give an alias to the copy of your table being joined.

Please find the query below - here both sides were aliased:

SELECT 
    history_AB.pKey
FROM 
    history  AS  history_AB
    INNER JOIN history  AS  history_CD 
        ON history_AB.pKey = history_CD.pKey 
WHERE 
    (history_AB.fieldName = 'A' AND history_AB.oldValue LIKE 'B%')
    AND (history_CD.fieldName = 'C' AND history_CD.oldValue LIKE 'D%')

In the WHERE clause there are conditions you mentioned in your question:

(...) say we find that we have a row in the history table with fieldName of A and old Value of B, the result will only be valid if the pKey associated with that row also turns up in the search for rows with a fieldName of C and oldValue of D

I hope I understood the problem well and it might help you some way.