CODEWITHSUNDEEP
sql-serverjoin
Hjh Mimi Zol
Hjh Mimi Zol

Reputation: 1

Join 3 tables empty

Can anyone help? I'm a noob on scripts.. The script below (without the BOLDED section gives me empty result) and if i include the BOLDED section, it tells me too many values.

Actually for the bolded section, what i wanted is to extract from three different tables.

TABLE A (OR_ORDER) it consists of 

    ID | CLASS | FACILITY | SOURCE_TYPE | SOURCE_CODE 
    101     IP       XY         N            W010
    102     IP       XY         N            W023 
    103     OP       SX         C            E122 
    114     OP       XY         C            R123

TABLE D (IP_NURSING_UNIT) it consists of 

NURSING_UNIT_CODE | SOURCE_TYPE | LONG_DESC
W010                     N         Ward 10
W023                     N         Ward 23

TABLE H (OP_CLINIC) it consists of 

CLINIC_CODE | SOURCE_TYPE | LONG_DESC
E122              C         Eye Clinic
R123              C         Dental

I want the expected output to be as below..just adding on long_desc column... depending on source_type. If source_type is N, then it should extract data from table D ..and if source_type is C, then it should extract data from table H..but I don't know how to join them..

ID | CLASS | FACILITY | SOURCE_TYPE | SOURCE_CODE | LONG_DESC
101     IP       XY         N            W010        Ward 10
102     IP       XY         N            W023        Ward 23
103     OP       SX         C            E122        Eye Clinic
114     OP       XY         C            R123        Dental

Query:

SELECT 
    A.ID, A.CLASS, C.FACILITY_NAME, A.SOURCE_TYPE,
    (SELECT a.source_code, d.LONG_DESC 
     FROM OR_ORDER A 
     INNER JOIN IP_NURSING_UNIT D ON a.source_code = d.nursing_unit_code 
     INNER JOIN OP_CLINIC H ON a.source_code = h.clinic_code),
    B.ORDER_ID, B.ORDER_CATALOG_CODE, B.CATALOG_DESC,
    B.START_DATE_TIME, B.END_DATE_TIME, B.ORDER_QTY,
    F.LONG_DESC, G.PUBLIC_PRICE, E.PRACTITIONER_NAME
FROM 
    OR_ORDER A, OR_ORDER_LINE B, SM_FACILITY_PARAM C, 
    IP_NURSING_UNIT D, AM_PRACTITIONER E, AM_UOM F, 
    BL_ST_ITEM_BY_PERIOD  G, OP_CLINIC H
WHERE   
    A.CLASS = 'OP'    
    AND A.ORDER_ID = B.ORDER_ID    
    AND A.ORDERING_FACILITY_ID = C.FACILITY_ID     
    AND A.ORDERING_FACILITY_ID = D.FACILITY_ID     
    AND A.ORDERING_FACILITY_ID = H.FACILITY_ID     
    AND A.SOURCE_CODE = D.NURSING_UNIT_CODE     
    AND A.SOURCE_CODE =H.CLINIC_CODE     
    AND B.ORD_PRACT_ID = E.PRACTITIONER_ID     
    AND (G.item_code = B.ORDER_CATALOG_CODE  
    AND G.OPERATING_FACILITY_ID = 'RP'  AND G.EFFECTIVE_TO_DATE IS NULL)
    AND B.ORDER_UOM = F.UOM_CODE AND B.ORD_DATE_TIME BETWEEN TO_DATE('05/02/2016 08:20:00', 'MM/DD/YYYY HH24:MI:SS') AND

TO_DATE('05/02/2016 08:30:00', 'MM/DD/YYYY HH24:MI:SS') ORDER BY B.ORD_PRACT_ID

Any help is greatly appreciated. Thanks in advance

Upvotes: 0

Views: 43

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

You would appear to want a left join:

select o.*, coalesce(nu.long_desc, c.long_desc) as long_desc
from or_order o left join
     ip_nursing_unit nu
     on o.source_code = nu.source_code and
        o.source_type = nu.source_type left join
     op_clinic c
     on o.source_code = c.source_code and
        o.source_type = c.source_type;

Your query is way too complicated. In addition, it does not use proper, explicit JOIN syntax. Simple rule: Never use commas in the FROM clause. Always use proper, explicit JOIN syntax.

You may have other conditions you want to add to the query.

Upvotes: 2

Related Questions