C
C
cicatrix2022-03-11 13:43:07
SQL
cicatrix, 2022-03-11 13:43:07

SQL two join in the same table?

Hello, there is an items table:

item_id uniqueidentifier not null constraint pk_items primary key,
firstDocument uniqueidentifier not null,
lastDocument uniqueidentifier not null


I have a document table
docid uniqueidentifier not null constraint pk_docs primary key,
op_type int not null


We need to return the operation codes for the first and last documents.
There is no way out other than using join twice. Do I understand correctly that this will actually happen 2 times slower?

select 
    i.item_id, 
    i.firstDocument, 
    fd.op_type, 
    i.lastDocument,
    ld.op_type
from 
    items i
left join documents fd on fd.docid = i.firstDocument
left join documents ld on ld.docid = i.lastDocument

Answer the question

In order to leave comments, you need to log in

2 answer(s)
K
Konstantin Tsvetkov, 2022-03-19
@cicatrix

SELECT items.item_id AS Item, FirstDoc.op_type AS FirstDocOper, LastDoc.op_type AS LastDocOper
  FROM items 
    INNER JOIN documents AS FirstDoc ON items.firstDocument = FirstDoc.docid 
    INNER JOIN documents AS LastDoc ON items.lastDocument = LastDoc.docid

R
res2001, 2022-03-11
@res2001

Do I understand correctly that this will actually happen 2 times slower?

Naturally.
But according to docid you have an index, so it should be fast enough.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question