CODEWITHSUNDEEP
sqlms-accessmysql
Rico
Rico

Reputation: 234

SQL - Finding transaction that contain 2 or more item using query

I have this table :

TABLE Transaction
Trans_ID    Name    Value    Total_Item  
100          I1     0.33333333        3  
100          I2     0.33333333        3  
100          I5     0.33333333        3  
200          I2     0.5               2  
200          I5     0.5               2  
300          I2     0.5               2  
300          I3     0.5               2  
400          I1     0.33333333        3  
400          I2     0.33333333        3  
400          I4     0.33333333        3  
500          I1     0.5               2  
500          I3     0.5               2  
600          I2     0.5               2  
600          I3     0.5               2  
700          I1     0.5               2  
700          I3     0.5               2  
800          I1     0.25              4  
800          I2     0.25              4  
800          I3     0.25              4  
800          I5     0.25              4  
900          I1     0.33333333        3  
900          I2     0.33333333        3  
900          I3     0.33333333        3  
1000         I1     0.2               5  
1000         I2     0.2               5  
1000         I4     0.2               5

I need to find what transaction containing some item. EX: 2 item combination

I1 and I2 and keeping only related item value;

Trans_ID    Name    Value    Total_Item  
100          I1     0.33333333        3  
100          I2     0.33333333        3  
400          I1     0.33333333        3  
400          I2     0.33333333        3  
800          I1     0.25              4  
800          I2     0.25              4    
900          I1     0.33333333        3  
900          I2     0.33333333        3    
1000         I1     0.2               5  
1000         I2     0.2               5

note that i only show transaction containing both item.

or perhaps 3 item combination I1,I2 and I3

800          I1     0.25              4  
800          I2     0.25              4  
800          I3     0.25              4   
900          I1     0.33333333        3  
900          I2     0.33333333        3  
900          I3     0.33333333        3

How do i code that in sql? i'm working with msaccess)

Upvotes: 0

Views: 662

Answers (2)

Fionnuala
Fionnuala

Reputation: 91366

These will work in Access:

SELECT * FROM Transaction t 
WHERE t.Trans_ID IN 
    (SELECT t1.Trans_ID
     FROM (SELECT *
           FROM Transaction 
           WHERE [Name]="I1")  AS t1 
     INNER JOIN 
          (SELECT *
           FROM Transaction 
           WHERE [Name]="I2")  AS t2 
     ON t1.Trans_ID = t2.Trans_ID)

AND t.Name IN ("I1","I2")


SELECT * FROM Transaction t 
WHERE t.Trans_ID IN 
    (SELECT t1.Trans_ID
     FROM ((SELECT *
           FROM Transaction 
           WHERE [Name]="I1")  AS t1 
     INNER JOIN 
          (SELECT *
           FROM Transaction 
           WHERE [Name]="I2")  AS t2 
     ON t1.Trans_ID = t2.Trans_ID)
     INNER JOIN 
          (SELECT *
           FROM Transaction 
           WHERE [Name]="I3")  AS t3 
     ON t1.Trans_ID = t3.Trans_ID )

AND t.Name IN ("I1","I2","I3")

Upvotes: 1

Andomar
Andomar

Reputation: 238116

For a two item combination, search for matching rows and ensure all conditions are met in a having clause:

select  Trans_ID
from    `Transaction`
where   Name in ('I1','I2')
group by
        Trans_ID
having  COUNT(distinct Name) = 2

For a three item combination, check for three instead of two matches:

select  Trans_ID
from    `Transaction`
where   Name in ('I1','I2','I3')
group by
        Trans_ID
having  COUNT(distinct Name) = 3

If you're interested in all columns, place that query in a subquery:

select  *
from    `Transaction`
where   Trans_ID in
        (
        select  Trans_ID
        from    `Transaction`
        where   Name in ('I1','I2','I3')
        group by
                Trans_ID
        having  COUNT(distinct Name) = 3
        )

Upvotes: 0

Related Questions