SQL Return multiple rows based on results of other row

Question

I'm trying to see what other SKUs have been purchased if SKU A was purchased.

What I have so far is something like:

Select *
    From Test 
    Where Transaction ID = Sku 'A'

but of course the where statement doesn't work. I don't have the ability to create new tables or anything but I think it should be possible to just do a query? Any help would be appreciated as I'm pulling my hair out here!

Thanks!

Table Name: Test (Original Image):

+ -------------- + --- + ----------------- +
| Transaction ID | SKU | Desired in Output |
+ -------------- + --- + ----------------- +
| 1              | A   | Y                 |
| 1              | B   | Y                 |
| 2              | A   | Y                 |
| 2              | D   | Y                 |
| 3              | F   | N                 |
| 3              | G   | N                 |
+ -------------- + --- + ----------------- +

Please read meta.stackoverflow.com/questions/285551/… and the accepted answer — user330315
– user330315, Commented Feb 27, 2017 at 21:17

KindaTechy · Accepted Answer · 2017-02-27 20:58:54Z

1

You could use a subquery

Select *
    From Test 
    Where Transaction_ID in (
        select Transaction_ID
            from Test
            where SKU = 'A'
    )

answered Feb 27, 2017 at 20:58

KindaTechy

1,1739 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SqlZim · Accepted Answer · 2017-02-27 17:30:39Z

0

You can use exists() in your where like so:

select *
from t
where exists (
  select 1
  from t as i
  where i.TransactionId = t.TransactionId
    and Sku = 'A'
);

answered Feb 27, 2017 at 17:30

SqlZim

38.2k6 gold badges45 silver badges61 bronze badges

Comments

Jonathan · Accepted Answer · 2017-02-27 18:50:29Z

0

Using an inner join to select Transaction id's that contain A:

create table #test
(
Transaction_id int,
Sku varchar(1)
)

insert into #test (Transaction_id, Sku) values (1, 'A')
insert into #test (Transaction_id, Sku) values (1, 'B')
insert into #test (Transaction_id, Sku) values (2, 'A')
insert into #test (Transaction_id, Sku) values (2, 'D')
insert into #test (Transaction_id, Sku) values (3, 'F')
insert into #test (Transaction_id, Sku) values (3, 'G')

select t.Transaction_id, t.Sku
from #test t
join 
(
    select Transaction_id
    from #test
    where Sku = 'A'

) x on x.Transaction_id = t.Transaction_id

drop table #test

answered Feb 27, 2017 at 18:50

Jonathan

931 silver badge6 bronze badges

Collectives™ on Stack Overflow

SQL Return multiple rows based on results of other row

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related