CODEWITHSUNDEEP
sqloracle-database
PixelPaul
PixelPaul

Reputation: 2801

Returning multiple columns with subquery and 'where in'

I'm having some trouble getting a select query to return the data I need. See the simplified example below.

Sku table

Sku         SkuId
==================
ABC-123     123
DEF-456     456
GHI-789     789
JKL-987     987

SkuCombo table

SkuId       SkuComboId  Qty
===========================
123         456         1
123         789         2
123         987         1

A "combo" item is a sku number given to an item that consists of multiple items. For example, for the sku ABC-123, you would need to look up the Sku from the Sku table to get the SkuId(123), then use the SkuId to get the SkuComboIds (456, 789, 987) from the SkuCombo. Then use those on the Sku table to get he corresponding Sku. The following query almost gets me there, but I don't know how to get the Qty column from the SkuCombo table?:

select * 
from Skus 
where SkuId in (Select SkuComboId 
                from SkuCombo  sc
                join Skus s on s.SkuId = sc.SkuId
                where s.Sku = 'ABC-123')

Desired result

Sku         SkuId       Qty
============================
DEF-456     456         1
GHI-789     789         2
JKL-987     987         1

Upvotes: 2

Views: 5951

Answers (3)

Amrita Srivastava
Amrita Srivastava

Reputation: 384

This can be done easily done using inner join as there are no null values in either of the table.

SELECT s.Sku, s.SkuId, sc.Qty
FROM SkuCombo sc
    INNER JOIN Skus s ON sc.SkuComboId = s.SkuId

Upvotes: 2

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You need SELF JOIN :

select sk1.Sku , sk1.SkuId, skc.qty
from Sku sk inner join 
     SkuCombo skc
     on skc.SkuId = sk.SkuId inner join 
     Sku sk1
     on sk1.SkuId = skc.SkuComboId;

Upvotes: 6

LindsayScott23
LindsayScott23

Reputation: 43

Not sure if I'm oversimplifying, or don't understand your question 100%, but wouldn't an Inner Join be the answer here:

Select *
from Skus s
join SkuCombo sc on s.SkuId = sc.SkuComboId

Upvotes: 2

Related Questions