Selecting parent records and child but leaving fields blank

Transact-SQL
1

I am trying to select parent records if they meet a specific criteria. There is a join to a child table and I want to return two fields from the child table but I only want to return a value in those fields if they match my criteria. If they don't match the criteria I just want them to be blank, but still select the parent record. Hope that makes sense.

worders.Date, worders.WONum1, worders.WONum, worders.TaskNum, worders.TaskDescription, worders.WODescription, worders.WOType, worders.EstDowntime,
worders.Priority, worders.ScheduledStartDate, worders.CompletionDate, worders.ScheduledCompletionDate, worders.PrintStatus, worders.Comments,
wostatus.WOStatus, equipment.EquipmentNum, equipment.Description, wooriginator.WOOriginator, woassignby.WOAssignBy, equipment.ModelNum,
equipment.SerialNum, equipment.Manufacturer, woparts.PartNum, woparts.PartDescription, assignto.AssignedTo, wolabor.EstimatedLabor, wolock.Sequence,
wolock.WOProcedure, woparts.ComponentID, sparts.Bin, equipspec.SpecType, equipspec.Specification
FROM equipspec RIGHT OUTER JOIN
equipment ON equipspec.EquipmentID = equipment.EquipmentID RIGHT OUTER JOIN
sparts INNER JOIN
woparts ON sparts.ComponentID = woparts.ComponentID INNER JOIN
wolock INNER JOIN
wooriginator INNER JOIN
worders INNER JOIN
wostatus ON worders.WOStatusID = wostatus.WOStatusID ON wooriginator.WOOriginatorID = worders.WOOriginatorID INNER JOIN
woassignby ON worders.WOAssignID = woassignby.WOAssignID ON wolock.WONum = worders.WONum INNER JOIN
wolabor ON worders.WONum = wolabor.WONum INNER JOIN
assignto ON wolabor.AssignID = assignto.AssignID ON woparts.WONum = worders.WONum ON equipment.EquipmentID = worders.EquipmentID
WHERE (worders.PrintStatus = 1) AND (wostatus.WOStatus = 'Open') OR (equipspec.SpecType = 'Water Pressure' OR
equipspec.SpecType = 'Air Pressure' OR equipspec.SpecType = 'Electrical' OR equipspec.SpecType = 'Hydraulic Pressure')

This is what I have right now. Sorry this query is huge. Basically I want all parent records (worders) who have a print status of 1 and WOStatus is Open. On the child table (equipspec) I want to only return values in the SpecType field that = 'Air Pressure', 'Water Pressure', Electrical' or 'Hydraulic Pressure'. I still want to get all records from the parent table (worders) even if the child doesn't have a match to the criteria but I would want the SpecType and Specification fields to be Null unless they equal one of the four requested SpecTypes. Each Equipment could have multiples of these four SpecTypes.

Hope that made sense.
Thanks,
Stacy

2

Please consider formating your sql - to help, [Poor Man's T-SQL Formatter] (http://poorsql.com/) can help.
Your joins are really messed up, but as far as I can gather, this might get you the result you're looking for:

select wo.date
      ,wo.wunum1
      ,wo.wonum
      ,wo.tasknum
      ,wo.taskdescription
      ,wo.wodescription
      ,wo.wotype
      ,wo.estdowntime
      ,wo.priority
      ,wo.scheduledstartdate
      ,wo.completiondate
      ,wo.scheduledcompletiondate
      ,wo.printstatus
      ,wo.comments
      ,wo.wostatus
      ,e.equipmentnum
      ,e.description
      ,woo.wooriginator
      ,woa.woassignby
      ,e.modelnum
      ,e.serialnum
      ,e.manufacturer
      ,wop.partnum
      ,wop.partdescription
      ,at.assignedto
      ,wola.estimatedlabor
      ,wolo.sequence
      ,wolo.woprocedure
      ,wop.componentid
      ,sp.bin
      ,es.spectype
      ,es.specification
  from worders as wo
       inner join wostatus as wos
               on wos.wostatusid=wo.wostatusid
              and wos.wostatus='Open'
       inner join woparts as wop
               on wop.wonum=wo.wonum
       left outer join sparts as sp
                    on sp.componentid=wop.componentid
       inner join wooriginator as woo
               on woo.wooriginatorid=wo.wooriginatorid
       inner join woassignby as woa
               on woa.woassignid=wo.woassignid
       inner join wolabor as wola
               on wola.wonum=wo.wonum
       inner join assignto as at
               on at.assignid=wola.assignid
       inner join wolock as wolo
               on wolo.wonum=wo.wonum
       left outer join equipment as e
                    on e.equipmentid=wo.equipmentid
       left outer join equipspec as es
                    on es.equipmentid=e.equipmentid
                   and es.spectype in ('Water Pressure'
                                      ,'Air Pressure'
                                      ,'Electrical'
                                      ,'Hydraulic Pressure'
                                      )
 where wo.printstatus=1
;
3

Might just be how my brain works, but I find LEFT OUTER JOINs much easier to work with / envisage than RIGHT OUTER JOINs