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:

fieldName of A AND oldValue of B
fieldName of C AND oldValue of D
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.

score 3 · Accepted Answer · 2015-01-21T05:38:55.743

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.

This is VERY close to what I want. Is it possible to select only the pKeys? — Klik, Jan 21 '15 at 00:04
Hi. Yes no problem. I've updated my answer to your remarks. Regards :) — , Jan 21 '15 at 05:38

BobRodes · Answer 2 · 2015-01-21T16:05:15.663

2

You have to identify the two instances of history as separate elements. Using your "ABCD" example instead of your statement:

SELECT * FROM history h1 
INNER JOIN history h2
ON h1.pKey=h2.pKey 
WHERE h1.fieldName = 'A' AND h1.oldValue = 'B' 
AND h2.fieldName = 'C' AND h2.oldValue = 'D'

edited Jan 21 '15 at 16:05

answered Jan 20 '15 at 23:13

BobRodes

5,990
2
24
26

TG and John beat me to it. :) – BobRodes Jan 20 '15 at 23:14
This was essentially what I was looking for. Thanks a lot, this is WAY more efficient than how I was doing it. – Klik Jan 21 '15 at 00:16
The second "oldvalue" should have the prefix "h2" instead of "h1". – Klik Jan 21 '15 at 14:26

score 1 · Answer 3 · answered Jan 20 '15 at 23:13

If you do a self-join then you generally need to assign table aliases in order to be able to refer unambiguously to any column of either table. Thus, you probably want something like this:

SELECT
  histA.pKey AS pKey,
  histA.newValue AS newA,
  histC.newValue AS newC,
FROM
  history histA
  INNER JOIN history histC
    ON histA.pKey = histC.pKey
WHERE
  histA.fieldName = 'A'
  AND histA.oldValue = 'B'
  AND histC.fieldName = 'C'
  AND histC.oldValue = 'D'

Of course, you can write the selction list as you want, including just selecting *, but I recommend at least assigning column aliases to help you sort out which data come from which table.

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

3 Answers3