MS SQL Server SELECT multiple rows with a compound key

Question

I'm trying to write a query that takes in a set of salesOrderId + lineNumber combo and returns that set in a single query. I can easily select one row at a time using the WHERE ... IN ... syntax (see below) but since I have a compound key, I'm not sure how to do it.

Using SQL Server, what are some ways to select multiple rows?

This query will back a function similar to this:

getSalesOrderLines([ 
  { SalesOrder: "C1001115", SalesOrderLine: 1 }, 
  { SalesOrder: "C1001115", SalesOrderLine: 3 },
  { SalesOrder: "C1001222", SalesOrderLine: 1 } 
])

And should return something like:

[
  { SalesOrder: 'C1001115', SalesOrderLine: 1, ... },
  { SalesOrder: 'C1001115', SalesOrderLine: 3, ... }
  { SalesOrder: 'C1001222', SalesOrderLine: 1, ... }
]

Working: select a single row:

SELECT *
FROM [SalesOrderDetailDetail]
WHERE [SalesOrderDetailDetail].[SalesOrder] = 'C1001115'
  AND [SalesOrderDetailDetail].[SalesOrderLine] = 1

Not working: tuple comparison / row constructor

SELECT *
FROM [SalesOrderDetail]
WHERE ([SalesOrderDetail].[SalesOrder], 
       [SalesOrderDetail].[SalesOrderLine]) 
   IN (('C1001115', 1))

This throws an error:

An expression of non-boolean type specified in a context where a condition is expected.

Not working: derived key:

SELECT 
    [SalesOrderDetail].[SalesOrder] + '-' + CAST([SalesOrderDetail]. [SalesOrderLine] AS VARCHAR) AS [comboKey],
    *
FROM [SalesOrderDetail]
WHERE comboKey IN (('C1001115-1'))

But I learned that you can't use a column alias directly in the WHERE clause

Possible duplicate of [WHERE col1,col2 IN (...) \[SQL subquery using composite primary key\]](https://stackoverflow.com/questions/4622453/where-col1-col2-in-sql-subquery-using-composite-primary-key) — Dale K, Dec 19 '18 at 01:40
What is the result set you are trying to retrieve? i.e. All details for a sales order?.... — cal5barton, Dec 19 '18 at 01:43

score 2 · Answer 1 · answered Dec 19 '18 at 02:20

You can achieve this a couple different ways.

You can do it like you first tried to do with the combokey string value. Yes you are right that you can't use the alias in the where statement but you can have the expression evaluate in the where statement.

SELECT 
  [SalesOrderDetail].[SalesOrder] + '-' + CAST([SalesOrderDetail]. [SalesOrderLine] AS VARCHAR) AS [comboKey],
  *
FROM [SalesOrderDetail]
WHERE ([SalesOrderDetail].[SalesOrder] + '-' + CAST([SalesOrderDetail]. [SalesOrderLine]) IN (('C1001115-1')

Or you can use multiple Or Statements to get the desired result set.

SELECT *
FROM [SalesOrderDetail]
WHERE ([SalesOrderDetail].[SalesOrder] = 'C1001115' AND [SalesOrderDetail].SalesOrderLine] = 1) 
OR ([SalesOrderDetail].[SalesOrder] = 'C1001115' AND [SalesOrderDetail].SalesOrderLine] = 3)

score 0 · Answer 2 · answered Dec 19 '18 at 02:58

One method uses cross join:

SELECT *
FROM SalesOrderDetail sod CROSS JOIN
     (VALUES ('C1001115', 1)) v(SalesOrder, SalesOrderLine)
     ON v.SalesOrder = sod.SalesOrder AND
        v.SalesOrderLine = sod.SalesOrderLine;

You an add more pairs to the VALUES() list, up to an arbitrary number.

score 0 · Answer 3 · answered Dec 19 '18 at 02:58

You can create a table in a CTE or SubQuery, then join to it.

WITH selList as (
Select 'C1001115' as SalesOrder, 1 as SalesOrderLine
UNION ALL Select 'C1001115', 3
UNION ALL Select 'C1001222', 1
)
SELECT D.*
FROM [SalesOrderDetailDetail] D
    INNER JOIN selList L on L.SalesOrder = D.SalesOrder and L.SalesOrderLine = D.SalesOrderLine

or

SELECT D.*
FROM [SalesOrderDetailDetail] D
    INNER JOIN 
        (
            Select 'C1001115' as SalesOrder, 1 as SalesOrderLine
            UNION ALL Select 'C1001115', 3
            UNION ALL Select 'C1001222', 1
        ) L on L.SalesOrder = D.SalesOrder and L.SalesOrderLine = D.SalesOrderLine

This is clumsy when writing a straight query, as above. But if you are going to make a stored procedure, you can remove the selection list creation, and pass the wanted row keys to the stored procedure as a TVP (Table Value Parameter) instead. Depending on where you are getting your selection details from, this may be the easiest thing for you to do, particularly if you may have large numbers of rows to select - you don't need to ever change the SQL, only give it a list of keys.

You would need to look up TVPs, but in brief, you would create a User Defined Table Type with your key columns, then your stored procedure would have a READONLY parameter of that type, and your query inside the stored procedure would join to the parameter for selection. When you call the SP, you would pass the selection data though as a table.

score 0 · Answer 4 · answered Dec 19 '18 at 03:35

My answer is a little different than the others. It involves a little coordination between the app developer and you, the data developer.

You said that you want the query to back this function:

getSalesOrderLines([ 
  { SalesOrder: "C1001115", SalesOrderLine: 1 }, 
  { SalesOrder: "C1001115", SalesOrderLine: 3 },
  { SalesOrder: "C1001222", SalesOrderLine: 1 } 
])

That just so happens to look a LOT like JSON. I realize it probably isn't, but here's the thing, if you can get the app to send you that data as a JSON (read: NVARCHAR(MAX)) string, AND you're on SQL 2016 or higher, then you can leverage the incredible power of native JSON in SQL Server.

But first, let's make a sample orders table and populate it with some test data:

CREATE TABLE #SalesOrderDetail
(
    SalesOrder VARCHAR(10),
    SalesOrderLine INT,
    ItemNumber INT
)
INSERT INTO #SalesOrderDetail
(
    SalesOrder
    ,SalesOrderLine
    ,ItemNumber
)
VALUES
('C1001115', 1, 100),
('C1001115', 2, 200),
('C1001115', 3, 300),
('C1001222', 1, 9832475),
('C1001222', 2, 634),
('C1001222', 3, 73546)

Next, let's assume that your query is going to live in a stored proc, and that proc has an input parameter called @ParamJSON. I don't want to write a proc for this example, so allow me to just declare a version of the parameter:

DECLARE @ParamJSON NVARCHAR(MAX) = N'
[
  { "SalesOrder": "C1001115", "SalesOrderLine": 1 },
  { "SalesOrder": "C1001115", "SalesOrderLine": 3 },
  { "SalesOrder": "C1001222", "SalesOrderLine": 1 } 
]'

Finally, we're going to shred the JSON using OPENJSON(), and then join to our sales table:

SELECT 
    s.*
FROM OPENJSON(@ParamJSON) 
WITH (
        SalesOrder varchar(10) '$.SalesOrder',
        SalesOrderLine int '$.SalesOrderLine'
    ) p
INNER JOIN #SalesOrderDetail s ON s.SalesOrder = p.SalesOrder AND s.SalesOrderLine = p.SalesOrderLine

That will yield the following result set:

SalesOrder  | SalesOrderLine    | ItemNumber
------------|-------------------|-----------
C1001115    | 1                 | 100
C1001115    | 3                 | 300
C1001222    | 1                 | 9832475

Neat, right? Well, we can even format the results like you are requesting, by converting the entire result set to JSON. Just add FOR JSON AUTO at the end of the SELECT statement, like this:

SELECT 
    s.*
FROM OPENJSON(@ParamJSON) 
WITH (
        SalesOrder varchar(10) '$.SalesOrder',
        SalesOrderLine int '$.SalesOrderLine'
    ) p
INNER JOIN #SalesOrderDetail s ON s.SalesOrder = p.SalesOrder AND s.SalesOrderLine = p.SalesOrderLine
FOR JSON AUTO

The result of that query will be a JSON-formatted string that looks like this (extra whitespace added for readability):

[
 {"SalesOrder":"C1001115","SalesOrderLine":1,"ItemNumber":100}, 
 {"SalesOrder":"C1001115","SalesOrderLine":3,"ItemNumber":300},
 {"SalesOrder":"C1001222","SalesOrderLine":1,"ItemNumber":9832475}
]

MS SQL Server SELECT multiple rows with a compound key

4 Answers4