SQL join in the same table

advertisements

This question already has an answer here:

  • What's the best way to join on the same table twice? 5 answers

I have a table that store information about transactions, where the KeyInfo column is not always available but a GUID is generated for all the entries in the same transaction.

    GUID   |    KeyInfo    |     Message
================================================
    123456 |    No Info    |  Sample message 1
    123456 |    No Info    |  Sample message 2
    123456 |    Test-1     |  Sample message 3
    123456 |    No Info    |  Sample message 4
    321654 |    No Info    |  Sample message 5
    321654 |    No Info    |  Sample message 6
    321654 |    Test-2     |  Sample message 7
    321654 |    No Info    |  Sample message 8
    789456 |    Test-1     |  Sample message 1
    789456 |    No Info    |  Sample message 2
    789456 |    Test-1     |  Sample message 3
    789456 |    No Info    |  Sample message 4

Currently I can do a search like this:

select GUID, KeyInfo, Message from MyTable where KeyInfo = 'Test-1'

This only returns two rows

GUID   |    KeyInfo    |     Message
================================================
123456 |    Test-1     |  Sample message 3
789456 |    Test-1     |  Sample message 3

But I need a query that returns all the rows that belongs to one transaction (same GUID), something like this

GUID | KeyInfo | Message

123456 |    Test-1     |  Sample message 1
123456 |    Test-1     |  Sample message 2
123456 |    Test-1     |  Sample message 3
123456 |    Test-1     |  Sample message 4
789456 |    Test-1     |  Sample message 1
789456 |    Test-1     |  Sample message 2
789456 |    Test-1     |  Sample message 3
789456 |    Test-1     |  Sample message 4

Any ideas on how to achieve this?


  select GUID, KeyInfo, Message from MyTable where GUID
IN(SELECT GUID from MyTable Where KeyInfo = 'Test-1')

i think abover query will have better performance