How to use Multi-Valued Subqueries in MS SQL

Question

Hello Folks,

I have an issue to use Multi-Valued Subqueries in my select statement.

I want to assign a value for each row but it obviously rejects it because my subquery returns multiple values.

It failed to run with this error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Let me post it ... and maybe someone could give me a hint,

Thanks,

Answer 1

First some house rules: You posted an Answer. Answers refer to answers to the original question. Not answers to requests for clarification that we may post in a comment. I have converted your Answer to a Comment. You would only post an Answer if you find the solution to the problem yourself.

Next, you want a result set with seven columns. In a result set, each column holds an atomic value. That is, a column cannot be multi-valued. That is, you cannot return something like:

(1, '2023-07-02', [1, 2, 3, 90, 34], 98, 123, '2023-07-07 12:12:12', 'That site']

If you want all the individual thawcounts, you will need to produce as many rows as there are thawcounts. In that case you would move tables in the FROM clause in the subquery to the main FROM clause of the query. But that looks a little funny to me. (Because in that case, I would also expect you to return some more information about the inventory transactions.)

I don't know anything about your tables or business needs, but what possibly make sense is to return the total thawcount. That is would change SELECT it.THAWCOUNT in the subquery with SELECT SUM(it.THAWCOUNT.

But I'm still guessing a bit here, because your train of thoughts is still not clear to me.

Answer 2

I reformatted your query to make it readable:

SELECT d.ORIG_SAMPLE_ID,        
       tr.RCV_DT,        
       (SELECT it.THAWCOUNT         
       FROM    inventory i              
       JOIN    INVENTORY_TRANSACTIONS it  ON it.INVENTORYID = i.INVENTORYID 
                                         AND it.FLAG_CURRENT = 1              
       JOIN    VA_SHIPPING_MANIFEST m ON m.CELL_ID = i.EXTERNAL_CODE
       JOIN    deleteme d ON d.SHIPPING_ID = m.SHIPPING_ID),
       m.CELL_ID, 
       m.SHIPPING_ID, 
       d.LAST_TOUCH, 
       vtc.SITE_NAME 
FROM   TRANSACTION_RECEPTION tr      
JOIN   TRANSACTIONS trans ON trans.TRANSACTION_ID = tr.RECEPTION_ID      
JOIN   INVENTORY_TRANSACTIONS it ON it.TRANSACTION_ID = tr.TRANSACTION_ID
JOIN   inventory i ON i.INVENTORYID = it.INVENTORYID
JOIN   VA_SHIPPING_MANIFEST m ON m.ORIG_SAMPLE_ID = i.EXTERNAL_CODE
JOIN   VA_TUBE_COLLECTION vtc ON vtc.INVENTORYID = i.INVENTORYID
JOIN   deleteme d ON d.SHIPPING_ID = m.SHIPPING_ID

And what I see now is that your subquery is not correlated to the outer query. This means that if it would somehow work as you wanted, it would produce the same set of values for all rows. Which typically does not make sense.

Also, the THAWCOUNTs come from the INVENTORY_TRANSACTONS table, which you already have in the main query. So why not simply say:

SELECT d.ORIG_SAMPLE_ID,        
       tr.RCV_DT,        
       it.THAWCOUNT,         
       m.CELL_ID, 
       m.SHIPPING_ID, 
       d.LAST_TOUCH, 
       vtc.SITE_NAME 
FROM   TRANSACTION_RECEPTION tr      
JOIN   TRANSACTIONS trans ON trans.TRANSACTION_ID = tr.RECEPTION_ID      
JOIN   INVENTORY_TRANSACTIONS it ON it.TRANSACTION_ID = tr.TRANSACTION_ID
JOIN   inventory i ON i.INVENTORYID = it.INVENTORYID
JOIN   VA_SHIPPING_MANIFEST m ON m.ORIG_SAMPLE_ID = i.EXTERNAL_CODE
JOIN   VA_TUBE_COLLECTION vtc ON vtc.INVENTORYID = i.INVENTORYID
JOIN   deleteme d ON d.SHIPPING_ID = m.SHIPPING_ID