I would at first not try for a single select, but to get the result set right and then (perhaps) try to streamline the SQL.
Did not try to think your text through too much, but the main problem seems to be uncertain join criteria.
So I'd first identify both sets needing different join criteria, do each join and then union them.
Still hazy if the order needs to be checked if trandetail already has a direct join...
>I need to create a SQL Select that would return the following query:
>
>EQUIPMENT.EQUIP_ID, EQUIPMENT.EQUIP_DESC, TRANDETAIL.* FROM TRANDETAIL --- JOINS
>
>Structure of tables (simplified):
>
>TRANDETAIL EQUIP_PK
> ORDER_NO
>
>ORDERS ORDER_NO
> EQUIP_ID
>
>EQUIPMENT EQUIP_ID Char(15) - Unique field
> EQUIP_PK
> EQUIP_DESC Char(40) - Equipment descriptoin
>
>
>The challenge is that the SQL Select should select All Records from TRANDETAIL and records
>from EQUIPMENT either by the field TRANDETAIL.EQUIP_PK Jointed with EQUIPMENT.EQUIP_PK
>or by Joining ORDERS on ORDER_NO and then Joining EQUIPMENT on EQUIPMENT.EQUIP_ID with ORDERS_EQUIP_ID.
>Therefore, it looks like I will have the table EQUIPMENT twice and this creates many NULL values.
>
>Is my explanation too confusing? Any suggestions?