Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Joining two select statement into one

Status
Not open for further replies.

ms901Boss

Programmer
Mar 19, 2021
16
US
I have created two queries in Microsoft sql server. I have two select statements that I have joined together and that works. The issue I have is I need the data in description to match shakeweight or armweight.

If description = 'ARM SHANK' then shakeweight

if description = 'ARM SHANK' then armweight

Code:
select 
    (select CUSTOMER_NAME from JobscopeMeyer.dbo.IPJOBM where JOB_NUMBER like  '41081R%' ) as Customer,
    trim (SUBSTRING(p.CATALOGUE_NUMBER,7,2)) ArmLetter,p.CATALOGUE_NUMBER, p.CATEGORY,
    b.DESCRIPTION,b.WEIGHT as ShankWeight,p.weight as ArmWeight,b.SUB_PART_CAT_NO
    from 
    (select e.CATALOGUE_NUMBER,ep.CATEGORY,ep.WEIGHT ,ep.DESCRIPTION,e.SUB_PART_CAT_NO
    from jobscopeMeyer.dbo.epsubcm e
    join JobscopeMeyer.dbo.EPCOMPM  ep
    on e.SUB_PART_CAT_NO = ep.CATALOGUE_NUMBER
    where ep.DESCRIPTION IN         ('ARM SHANK' , 'MOUNT CHANNEL')
    )as b
     JOIN
    (Select p.CATALOGUE_NUMBER,p.CATEGORY,e.WEIGHT,p.DESCRIPTION,p.release,p.QTY_REQUIRED,p.LOCATION_CODE
    from JobscopeMeyer.dbo.EPCOMPM e
    join JobscopeMeyer.dbo.ppwom p
    on  e.catalogue_number = p.CATALOGUE_NUMBER
    and e.DESCRIPTION = p.DESCRIPTION
    where p.release like  '41081R%' 
    and p.CATEGORY = 'ARM'
    ) as p
    on p.CATALOGUE_NUMBER = b.CATALOGUE_NUMBER
    where p.release like  '41081R%'   
    and p.qty_required >0

I have attached what the data looks like now
 
Your requirement does not make sense.

If description = 'ARM SHANK' then shakeweight

if description = 'ARM SHANK' then armweight

Of course you can always use a case statement on the description to return what you need.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top