SQL join within same table

Question

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 B.GUID, A.KeyInfo, A.Message from MyTable A INNER JOIN MYTABLE B ON (A.GUID = B.GUID) where A.KeyInfo = 'Test-1'` — abhi, Apr 09 '14 at 19:57
So the KeyInfo is unique per GUID, i.e. all records for one GUID have the same KeyInfo or none? Is the KeyInfo NULL for those records or does it contain the text 'No Info'? — Thorsten Kettner, Apr 09 '14 at 20:10
Thanks I have tried with the query that abhi provided but I am getting duplicate rows. — axy108, Apr 09 '14 at 20:19
Hi Thorsten, Yes the KeyInfo is Unique per GUID, but there may be more rows with the same KeyInfo but different GUID (another transaction for the same item) and for other records include 'No Info'. It may be the case where multiple rows has the KeyInfo populated. I will update the question with a sample of this. — axy108, Apr 09 '14 at 20:22
@axy108, Sorry. I should have used an INNER query like Amrit has provided. My query will not work for your case. — abhi, Apr 09 '14 at 20:44

score 3 · Answer 1 · answered Apr 09 '14 at 20:23

3

Here is one way to do it...

SELECT *
FROM MyTable
WHERE GUID IN (
    SELECT GUID
    FROM MyTable
    WHERE KeyInfo = 'Test-1'
)

Unlike JOIN, you don't have to worry whether there is more than one row with KeyInfo = 'Test-1'.

answered Apr 09 '14 at 20:23

Branko Dimitrijevic

50,809
10
93
167

Thanks Branko I just pick Amrit anwser as he beat you by 1 min, but you are completely right about having more than one with the same KeyInfo. – axy108 Apr 09 '14 at 20:45
2

@axy108 For the record: Amrit was actually slower than me by about 1 min 30 s. – Branko Dimitrijevic Apr 09 '14 at 21:01

score 1 · Accepted Answer · answered Apr 09 '14 at 20:24

1

  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

answered Apr 09 '14 at 20:24

Amrit

423
6
22

Thanks Amrit this one does exactly what I was looking for! – axy108 Apr 09 '14 at 20:43

score 0 · Answer 3 · answered Apr 09 '14 at 19:59

0

Yes, you can get the data using a self-join, as below:

select 
mt1.GUID, mt1.KeyInfo, mt1.Message 
from MyTable mt1, MyTable mt2
where mt1.GUID    = mt2.GUID
and   mt2.KeyInfo = 'Test-1'

answered Apr 09 '14 at 19:59

Joseph B

5,519
1
15
19

Thanks Joseph but if I have more than one row with the same KeyInfo it returns duplicate rows. – axy108 Apr 09 '14 at 20:28
Yes. This performs a Cartesian join. – abhi Apr 09 '14 at 20:45

SQL join within same table

GUID | KeyInfo | Message

3 Answers3