Find 3 item association rules in SQL Server

Database:

Transaction  ProductID
 1              1000
 2              1000
 2              1001 
 3              1000
 3              1002
 4              1000
 4              1001
 5              1003

and L2 table:

PRODUCTID1  PRODUCTID2 
  1000       1001         
  1000       1002         

I want to self join L2.

ex; first row{1000,1001} and second row {1000,1002}-->result {1000,1001,1002}

How to find this result with a T-SQL statement? And find support(Count) value from my Transaction table? I mean {1000,1001,1002} count which sold together.

note: it's just sample database. I have a much bigger database for this.

+-------------+-----------+-----------------+---------+
| PRODUCTID1  | PRODUCTID2|   PRODUCTID3    | SUPPORT |
+-------------+-----------+-----------------+---------+
|      1000   |   1001    |       1002      |    0    |
+-------------+-----------+-----------------+---------+

Test Table:

create table transactions(
   ORDERID    INT,
   PRODUCTID INT
);

insert into transactions(ORDERID, PRODUCTID)
values ('1', '1000')
      ,('2', '1000')
      ,('2', '1001')
      ,('3', '1000')
      ,('3', '1002')
      ,('4', '1000')
      ,('4', '1001'),
       ('5', '1003');

CREATE TABLE L2 
   (PRODUCTID1 INT,
    PRODUCTID2 I开发者_高级运维NT)

INSERT INTO L2 (PRODUCTID1,PRODUCTID2)
VALUES (1000,1001),(1000,1002)

Thanks in advance.

I'm still struggling to understand exactly what you're trying to accomplish here, and I think you're probably making it a lot more complicated then it needs to be.

However, using your existing table setup, I think you can get the count of certain 3-product combinations sold together without even using L2, by doing something like this:

select
    T1.PRODUCTID AS PRODUCTID1,
    T2.PRODUCTID AS PRODUCTID2,
    T3.PRODUCTID AS PRODUCTID3,
    COUNT(DISTINCT T1.ORDERID) AS SUPPORT
FROM transactions T1
JOIN transactions T2 ON T2.PRODUCTID > T1.PRODUCTID AND T2.ORDERID = T1.ORDERID
JOIN transactions T3 ON T3.PRODUCTID > T1.PRODUCTID AND T3.PRODUCTID > T2.PRODUCTID AND T3.ORDERID = T1.ORDERID
GROUP BY T1.PRODUCTID, T2.PRODUCTID, T3.PRODUCTID

This will not return any records where SUPPORT would be 0 though. Only if there were at least 1 transaction where that product combination was sold would there by a row produced.

If this doesn't meet your needs, it would help if you could provide a better explanation of what you're trying to do any why. Also, is there a PRODUCT table? That might help.

In the test data you gave above, no 3-product combinations even exist (never more than 2 rows in transactions with the same ORDERID).