Merging the result of two INNER JOINS of the same table into one single temporary table in SQL Server

Question

I am trying to get all key-value pairs from a document which you can search for with a list of key-value pairs yourself.

For this I create a temporary table and a SELECT statement for each key-value pair provided like this:

DECLARE @DocMetaTemp TABLE
(
    DocumentsID int,
    DataKey varchar(255),
    DataValue varchar(255) 
);

INSERT INTO @DocMetaTemp
    SELECT docMeta01.DocumentsID, docMeta01.DataKey, docMeta01.DataValue
    FROM psp_doc.Documents as docs
    INNER JOIN psp_doc.DocumentMetadata as docMeta01 ON docMeta01.DocumentsID = docs.ID 
                                                     AND docMeta01.DataKey = 'testkey1' 
                                                     AND docMeta01.DataValue = 'testval1';

INSERT INTO @DocMetaTemp
    SELECT docMeta02.DocumentsID, docMeta02.DataKey, docMeta02.DataValue
    FROM psp_doc.Documents as docs
    INNER JOIN psp_doc.DocumentMetadata as docMeta02 ON docMeta02.DocumentsID = docs.ID 
                                                     AND docMeta02.DataKey = 'testkey2'  
                                                     AND docMeta02.DataValue = 'testval1';

select * from @DocMetaTemp

Which results in

but this looks very inefficent to me and also does not return the list of all key-value pairs that is assigned to the returned DocumentsIDs.

I tried to merge the query by adding the second INNER JOIN statement after the first one, but this results in an error as it does not seem to merge using the column names.

DECLARE @DocMetaTemp TABLE
(
    DocumentsID int,
    DataKey varchar(255),
    DataValue varchar(255) 
);

INSERT INTO @DocMetaTemp
SELECT  docMeta01.DocumentsID, docMeta01.DataKey, docMeta01.DataValue, docMeta02.DocumentsID, docMeta02.DataKey, docMeta02.DataValue
FROM psp_doc.Documents as docs
INNER JOIN psp_doc.DocumentMetadata as docMeta01 ON docMeta01.DocumentsID = docs.ID and docMeta01.DataKey = 'testkey1' and docMeta01.DataValue = 'testval1'
INNER JOIN psp_doc.DocumentMetadata as docMeta02 ON docMeta02.DocumentsID = docs.ID and docMeta02.DataKey = 'testkey2' and docMeta02.DataValue = 'testval1';

select * from @DocMetaTemp

results in

Column name or number of supplied values does not match table definition.

How can I do this in a single statement? And how do I get all key-value pairs based on the IDs returned?

you are inserting records with 6 fields in @DocMetaTemp but it only has 3 fields. There is no problem with your joins — GuidoG, Jul 24 '17 at 12:02
My question is how to merge both joins as they are both returning the some column names resulting in a single row with the columns appearing twice. — Kia, Jul 24 '17 at 12:05
If you want to combine the result of two queries with the same amount of columns you could use a "union". But for this problem can't you create one query by using "in"? INSERT INTO @DocMetaTemp SELECT docMeta01.DocumentsID, docMeta01.DataKey, docMeta01.DataValue FROM psp_doc.Documents as docs INNER JOIN psp_doc.DocumentMetadata as docMeta01 ON docMeta01.DocumentsID = docs.ID and docMeta01.DataKey in( 'testkey1','testkey2') and docMeta01.DataValue = 'testval1'; — Valderann, Jul 24 '17 at 12:08
No, as I need the documents which have BOTH keys in the result. "IN" would return all documents that have Key1 OR Key2. — Kia, Jul 24 '17 at 12:09
@Kia I edited my answer to this comment. If I understand you correct than the last shorter query in the answer might be what you are looking for. If it is not than please provide us with sample data to explain better what you want — GuidoG, Jul 24 '17 at 12:47

GuidoG · Answer 1 · 2017-07-24T14:59:40.420

If I understand you right you need to use UNION to get what you want.
This will get the 3 fields from both and in just one query.

DECLARE @DocMetaTemp TABLE
(
    DocumentsID int,
    DataKey varchar(255),
    DataValue varchar(255) 
);

INSERT INTO @DocMetaTemp

SELECT docMeta01.DocumentsID, docMeta01.DataKey, docMeta01.DataValue
FROM   psp_doc.Documents as docs
  INNER JOIN psp_doc.DocumentMetadata as docMeta01 
    ON docMeta01.DocumentsID = docs.ID 
   and docMeta01.DataKey = 'testkey1' 
   and docMeta01.DataValue = 'testval1'

union all

SELECT docMeta02.DocumentsID, docMeta02.DataKey, docMeta02.DataValue
FROM   psp_doc.Documents as docs
  INNER JOIN psp_doc.DocumentMetadata as docMeta02 
   ON docMeta02.DocumentsID = docs.ID 
  and docMeta02.DataKey = 'testkey2' 
  and docMeta02.DataValue = 'testval1';

select * from @DocMetaTemp

I do not know your data but from the info you gave us it might be much easier to do it like this : EDIT: (after reading your last comment I altered the where clause)

INSERT INTO @DocMetaTemp
SELECT docMeta01.DocumentsID, docMeta01.DataKey, docMeta01.DataValue
FROM   psp_doc.Documents as docs
  INNER JOIN psp_doc.DocumentMetadata as docMeta01 
    ON docMeta01.DocumentsID = docs.ID 
where  ( 
         (docMeta01.DataKey = 'testkey1' and docMeta01.DataValue = 'testval1')
         or     
         (docMeta01.DataKey = 'testkey2' and docMeta01.DataValue = 'testval1') 
       )

EDIT:
From your own answer I think you can improve efficienty by leaving out the temp tables:

SELECT DISTINCT
       t.DocumentsID,
       psp_doc.DocumentMetadata.DataKey, 
       psp_doc.DocumentMetadata.DataValue 
FROM 
    psp_doc.DocumentMetadata 
      LEFT JOIN ( SELECT docMeta01.DocumentsID
                  FROM   psp_doc.Documents as docs
                    INNER JOIN psp_doc.DocumentMetadata as docMeta01 
                      ON docMeta01.DocumentsID = docs.ID 
                     and docMeta01.DataKey = 'testkey1' 
                     and docMeta01.DataValue = 'testval1'
                  union
                  SELECT docMeta02.DocumentsID
                  FROM   psp_doc.Documents as docs
                    INNER JOIN psp_doc.DocumentMetadata as docMeta02 
                      ON docMeta02.DocumentsID = docs.ID 
                     and docMeta02.DataKey = 'testkey2' 
                     and docMeta02.DataValue = 'testval2'
                ) t
      ON t.DocumentsID = psp_doc.DocumentMetadata.DocumentsID

Or as Max suggested maybe even shorter by skipping the union like this:

SELECT DISTINCT
       t.DocumentsID,
       psp_doc.DocumentMetadata.DataKey, 
       psp_doc.DocumentMetadata.DataValue 
FROM 
    psp_doc.DocumentMetadata 
      LEFT JOIN ( SELECT docMeta01.DocumentsID
                  FROM   psp_doc.Documents as docs
                    INNER JOIN psp_doc.DocumentMetadata as docMeta01 
                      ON docMeta01.DocumentsID = docs.ID 
                  where  ( 
                           (docMeta01.DataKey = 'testkey1' and docMeta01.DataValue = 'testval1')
                           or     
                           (docMeta01.DataKey = 'testkey2' and docMeta01.DataValue = 'testval1')
                         ) 
                ) t
      ON t.DocumentsID = psp_doc.DocumentMetadata.DocumentsID

What is the difference between using a single INSERT INTO and UNION and my first approach? — Kia, Jul 24 '17 at 12:12
it is different because this is just one sql not 2. Only one insert statement not 2 — GuidoG, Jul 24 '17 at 12:13
In other words, it gives you the extra efficienty that you asked for — GuidoG, Jul 24 '17 at 12:14

Max · Accepted Answer · 2017-07-24T15:49:58.363

It's a little unclear to me, but it sounds like you want to be able to do this with an arbitrary number of key-Value pairs. In that case, it would probably be easier to populate a temp table with the key-values and join onto that. Something like:

DECLARE @KeyValyePairs TABLE ([Key] VARCHAR(255), [Value] VARCHAR(255));

INSERT INTO @KeyValuePairs (Key, Value)
VALUES
('testkey1', 'testval1'),
('testkey2', 'testval1') -- Or two or whatever
--(....., ....),.....

SELECT m.DocumentsID, m.DataKey, m.DataValue
FROM
    psp_doc.DocumentMetadata as m INNER JOIN
    @KeyValuePairs as kvp ON m.DataKey = kvp.Key AND m.DataValue = kvp.Value

You could either return this if that's what you wanted or put it in a temp table as before or use in a WITH statement, etc.

If you wanted to get the info for all documents from the list (which it also kind of sounded like from the question, you would do the following:

SELECT dm.DocumentID, dm.DataKey, dm.DataValue
FROM psp_doc.DocumentMetadata as dm
WHERE m.DocumentsID IN
(
    SELECT m.DocumentsID
    FROM
        psp_doc.DocumentMetadata as m INNER JOIN
        @KeyValuePairs as kvp ON m.DataKey = kvp.Key AND m.DataValue = kvp.Value
);

-- EDIT

From seeing your answer below where you use the JOIN and the UNION: You can skip the UNION and put both conditions in the JOIN. I put it in a comment, but it wasn't very readable. Note: in your example you could do this with a WHERE as you aren't using the documents table. This would skip the join entirely, which would save some code and probably (I think, it might be optimized away) run more efficiently. Here is the code with the join to select into your table. I've used a subquery as I think they are easier to read.

SELECT meta.DocumentsID, meta.DataKey, meta.DataValue
FROM psp_doc.DocumentMetadata meta
WHERE meta.DocumentsID IN
(
    SELECT m.DocumentsID
    FROM psp_doc.DocumentMetadata m
    WHERE
        (m.DataKey = 'testkey1' AND m.DataValue = 'testvalue1') OR
        (m.DataKey = 'testkey2' AND m.DataValue = 'testvalue2')
)

Works perfectly fine! Thank you! – Kia Jul 25 '17 at 04:56 — Kia, Jul 25 '17 at 04:56

score 1 · Answer 3 · answered Jul 24 '17 at 13:06

I used a mix of Max's and GuidoG's answers and came up with this code now that works:

DECLARE @DocMetaTemp TABLE
(
    DocumentsID int
);

INSERT INTO @DocMetaTemp

SELECT docMeta01.DocumentsID
FROM   psp_doc.Documents as docs
  INNER JOIN psp_doc.DocumentMetadata as docMeta01 
    ON docMeta01.DocumentsID = docs.ID 
   and docMeta01.DataKey = 'testkey1' 
   and docMeta01.DataValue = 'testval1'

union

SELECT docMeta02.DocumentsID
FROM   psp_doc.Documents as docs
  INNER JOIN psp_doc.DocumentMetadata as docMeta02 
   ON docMeta02.DocumentsID = docs.ID 
  and docMeta02.DataKey = 'testkey2' 
  and docMeta02.DataValue = 'testval2';

---

DECLARE @DocMetaTempResult TABLE
(
    DocumentsID int,
    DataKey varchar(255),
    DataValue varchar(255)
);

SELECT DISTINCT
    [@DocMetaTemp].DocumentsID,
    psp_doc.DocumentMetadata.DataKey, 
    psp_doc.DocumentMetadata.DataValue 
FROM 
    psp_doc.DocumentMetadata 
LEFT JOIN @DocMetaTemp ON [@DocMetaTemp].DocumentsID = psp_doc.DocumentMetadata.DocumentsID

If this is getting you the correct results than it might be possible to do it more efficient. Check out the edited part in my answer. The last query might just be what you are looking for without using temp tables. — GuidoG, Jul 24 '17 at 14:01
Also, instead of the union, you can skip the Union with: SELECT docMeta01.DocumentsID FROM psp_doc.Documents as docs INNER JOIN psp_doc.DocumentMetadata as docMeta01 ON docMeta01.DocumentsID = docs.ID and ( (docMeta01.DataKey = 'testkey1' and docMeta01.DataValue = 'testval1') OR (docMeta01.DataKey = 'testkey2' and docMeta01.DataValue = 'testval2')); — Max, Jul 24 '17 at 14:37

Merging the result of two INNER JOINS of the same table into one single temporary table in SQL Server

3 Answers3

Linked