SQL query with condition

Question

So I have a table for every item in my sales order, which contains the item ID or just a description if it's not a real item, the table looks like this:

SalesOrderLine:

|ID | SOI_IMA_RecordID | SOI_LineNbrTypeCode | SOI_MiscLineDescription |
|1  | 2                | Item                | XYZ                     |
|2  | NULL             | GL Acct             | Description             |
|3  | NULL             | GL Acct             | Descrip                 |
|4  | 20               | Item                | ABC                     |

What I want to do is, if it's not a real item (have the SOI_IMA_RecordID = NULL and/or SOI_LineNbrTypeCode = GL Acct) do not inner join with my Item table and get the SOI_MiscLineDescription instead of the item name (IMA_ItemName in my query).

My query is the following:

SELECT  SOH_ModifiedDate,
        SOM_SalesOrderID,
        IMA_ItemName,
        SOH_SOD_RequiredDate,
        SOD_RequiredDate,
        SOH_SOD_DockDate,
        SOD_DockDate,
        SOH_SOD_PromiseDate,
        SOD_PromiseDate,
        SOH_SOD_RequiredQty,
        SOD_RequiredQty,
        SOH_SOD_UnitPrice,
        SOD_UnitPrice
    FROM        WSI_SOH            SOH
    INNER JOIN  SalesOrderDelivery SOD   ON SOH.SOH_SOD_RecordID = SOD.SOD_RecordID
    INNER JOIN  SalesOrder         SO    ON SO.SOM_RecordID      = SOD.SOD_SOM_RecordID
    INNER JOIN  SalesOrderLine     SOI   ON SOI.SOI_RecordID     = SOD.SOD_SOI_RecordID
    INNER JOIN  Item               ITE   ON ITE.IMA_RecordID     = SOI.SOI_IMA_RecordID

How to do it?

Depending on your case you could use outer join for the other tables or split the fetch into 2 parts using union all and fetch the null rows separately — James Z
– James Z, Commented Jun 3, 2015 at 16:30

Rubens Farias · Accepted Answer · 2015-06-03 16:28:50Z

2

You can use the CASE statement in your SELECT:

SELECT CASE WHEN SOI_IMA_RecordID IS NULL OR
                 SOI_LineNbrTypeCode = 'GL Acct'
            THEN SOI_MiscLineDescription
            ELSE IMA_ItemName
       END ItemName,
       -- ...
FROM        WSI_SOH            SOH
INNER JOIN  SalesOrderDelivery SOD   ON SOH.SOH_SOD_RecordID = SOD.SOD_RecordID
INNER JOIN  SalesOrder         SO    ON SO.SOM_RecordID      = SOD.SOD_SOM_RecordID
INNER JOIN  SalesOrderLine     SOI   ON SOI.SOI_RecordID     = SOD.SOD_SOI_RecordID
LEFT  JOIN  Item               ITE   ON ITE.IMA_RecordID     = SOI.SOI_IMA_RecordID AND
                                        SOI_IMA_RecordID IS NOT NULL AND
                                        SOI_LineNbrTypeCode != 'GL Acct'

edited Jun 3, 2015 at 16:28

answered Jun 3, 2015 at 16:27

Rubens Farias

58k8 gold badges136 silver badges165 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dave Carlile Over a year ago

And left join the Item table.

shawnt00 Over a year ago

SOI_IMA_RecordID IS NOT NULL is redundant in the join condition. It's already implied by the comparison with ITE.IMA_RecordID.

Mureinik · Accepted Answer · 2015-06-03 16:27:51Z

A left join will allow you to join when the on condition is matched, and still display only the columns from the right side of the join when it isn't - in your case, when SOI_IMA_RecordID is null:

SELECT  SOH_ModifiedDate,
        SOM_SalesOrderID,
        IMA_ItemName,
        SOH_SOD_RequiredDate,
        SOD_RequiredDate,
        SOH_SOD_DockDate,
        SOD_DockDate,
        SOH_SOD_PromiseDate,
        SOD_PromiseDate,
        SOH_SOD_RequiredQty,
        SOD_RequiredQty,
        SOH_SOD_UnitPrice,
        SOD_UnitPrice
    FROM        WSI_SOH            SOH
    INNER JOIN  SalesOrderDelivery SOD   ON SOH.SOH_SOD_RecordID = SOD.SOD_RecordID
    INNER JOIN  SalesOrder         SO    ON SO.SOM_RecordID      = SOD.SOD_SOM_RecordID
    INNER JOIN  SalesOrderLine     SOI   ON SOI.SOI_RecordID     = SOD.SOD_SOI_RecordID
    LEFT JOIN   Item               ITE   ON ITE.IMA_RecordID     = SOI.SOI_IMA_RecordID

Ed B · Accepted Answer · 2015-06-03 16:32:26Z

0

Change your INNER JOIN to a LEFT JOIN and use CASE to get the value you want:

SELECT  SOH_ModifiedDate,
...
        CASE 
            WHEN IMA_ItemName IS NULL THEN SOI_MiscLineDescription 
            ELSE IMA_ItemName END AS ItemName,
...
FROM        WSI_SOH            SOH
...
LEFT JOIN Item ITE ON ITE.IMA_RecordID = SOI.SOI_IMA_RecordID AND SOI_IMA_RecordID IS NOT NULL

This way, it only joins to the Item table when there is a record to match, and when there is no matching record, the fields are NULL. The CASE picks the field from the Item table when it is not NULL, and the description otherwise.

Edit - I see two people got there before me whilst I was typing my answer!

answered Jun 3, 2015 at 16:32

Ed B

8165 silver badges14 bronze badges

Comments

Blamkin86 · Accepted Answer · 2015-06-03 16:33:30Z

0

Because you're using an INNER JOIN on item, any rows in your SalesOrderLine that do not have a corresponding ITE.IMA_RecordID won't appear in your final query.

Instead, use a LEFT JOIN on item, and either use a function that determines which description to use, or simply return both columns (the description from SalesOrder Line and the description from Item.)

I'm not too familiar with SQL Server functions, but in PostGres and MySql, these functions are like COALESCE() (returns first non-null value in list of fields)

I believe you may want to use the case statement here, something like

case when item.description!=null 
then item.description 
else SalesOrderLine.SOI_MiscLineDescription 
end

answered Jun 3, 2015 at 16:33

Blamkin86

1,0599 silver badges8 bronze badges

Collectives™ on Stack Overflow

SQL query with condition

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related