Joining two select statement into one

[ms901Boss]

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

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

https://files.engineering.com/getfi...9f-cc2a-4e1e-88ff-d9a2d825e118&file=Query.GIF

 

[kray4660]

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.