|
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
|
|
SlickEdit: Code in over 40 languages across 7 platforms. SlickEdit’s unmatched power, speed, and flexibility allows even the most accomplished developers to write better code faster. Download a free trial today! |
|
#1
|
|||
|
|||
|
JOIN with Subquery?
Here is my query:
SELECT B.SHNAME, B.SHADD1, B.SHADD2, B.SHCITY, B.SHSTAT, B.SHZIP FROM FILESIZE.PICKHEAD AS A LEFT JOIN FILES.SHIPMAST AS B ON B.SHIP# = A.SHIP AND B.SHCUS# = A.CUST WHERE A.ORD# = 270068 Gets me what I need, however, there is the occasion where A.SHIP with = 0, which means that I need to go to a 3rd table. How do I add the logic to say A.SHIP = 0 (SELECT NAME, ADD ..) and not the B.SHNAME |
|
#2
|
|||
|
|||
|
Here is what I ended up doing.
SELECT A.THSHIP, CASE WHEN A.THSHIP <> 0 THEN B.SHNAME ELSE C.CUNAME END AS NAME, CASE WHEN A.THSHIP <> 0 THEN B.SHADD1 ELSE C.CUADD1 END AS ADD1, CASE WHEN A.THSHIP <> 0 THEN B.SHADD2 ELSE C.CUADD2 END AS ADD2, CASE WHEN A.THSHIP <> 0 THEN B.SHCITY ELSE C.CUCITY END AS CITY, CASE WHEN A.THSHIP <> 0 THEN B.SHSTAT ELSE C.CUSTAT END AS STAT, CASE WHEN A.THSHIP <> 0 THEN B.SHZIP ELSE C.CUZIP END AS ZIP FROM FILESIZE.PICKHEAD A LEFT OUTER JOIN FILES.SHIPMAST B ON B.SHIP# = A.THSHIP AND B.SHCUS# = A.THCUST LEFT OUTER JOIN FILES.CUSTMAST C ON C.CUST# = A.THCUST WHERE A.THTIK# = 326911 |
![]() |
| Viewing: Dev Shed Forums > Databases > DB2 Development > JOIN with Subquery? |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|