CODEWITHSUNDEEP
sqlsubquery
rphello101
rphello101

Reputation: 1771

How do use a sub-query within a query in SQL?

I'm fairly new to SQL and am having trouble generating the correct information. I have a data set with FinishedGood part numbers and ProductClassCode, among other things. What I am looking for is all FinishedGood part numbers with multiple ProductClassCode's, one of which is 'WU'. I can run a query to find all ProductClassCode's equal to WU:

select finished_good
from FFTGGM.data_attributes_ext
where prodclass_cd = 'WU'

But I'm having trouble figuring out how to use that query to compare it to all FinishedGood's to generate a list of FinishedGood's with a ProdClasssCode of 'WU' AND something else. I know I can use it as a sub-query, but I'm unsure of how to get the correct order for the look up. Any advice?

-Edit-

Some sample data:

sample data

Upvotes: 0

Views: 77

Answers (2)

Darius X.
Darius X.

Reputation: 2937

You could use an IN clause or an EXISTS clause:

select *
from FFTGGM.data_attributes_ext
where finished_good in
(
  select distinct finished_good
  from FFTGGM.data_attributes_ext
  where prodclass_cd = 'WU'
)

or 

select *
from FFTGGM.data_attributes_ext A
where 
EXISTS (
  select finished_good
  from FFTGGM.data_attributes_ext B
  where A.finished_good=B.finished_good
    and prodclass_cd = 'WU'
)

If you only want finished goods which have 'WU" and also have another non-WU product-class, you could do two checks, like this:

select *
from FFTGGM.data_attributes_ext A
where 
EXISTS (
  select finished_good
  from FFTGGM.data_attributes_ext B
  where A.finished_good=B.finished_good
    and prodclass_cd = 'WU'
)
and 
EXISTS (
  select finished_good
  from FFTGGM.data_attributes_ext B
  where A.finished_good=B.finished_good
    and prodclass_cd <> 'WU'
)

Upvotes: 0

attach
attach

Reputation: 23

Or you could do:

where prodclass_cd in (select distinct prodclass_cd from prodclasstable)

Your criteria in the WHERE clause can then be dynamic.

Upvotes: 1

Related Questions