SQL Server : multiple column subquery

Question

Let's say I want to query the order number, product number, and quantity of any item in which the product number and quantity match both the product number and quantity of an item in ordid 365 AND the productCategory is "Electronics".

I've tried to do it as follows:

SELECT 
    ordid, prodid, qty
FROM 
    item
WHERE 
    ((prodid, qty) IN (SELECT prodid, qty
                       FROM item
                       WHERE ordid = 365)
     AND productCategory = "Electronics")

But I'm getting the following errors:

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

Incorrect syntax near the keyword 'and'.

Am I using the right T-SQL syntax to perform this kind of action?

Thanks in advance!

Does it make anyu sense right now? your FROM in query and subquery are the same. So, your subquery is basically all that you need. Are you using different tables in reality? — T.S.
– T.S., Commented Apr 13, 2021 at 18:19
Sorry but I think i'm missing something, how to I do it without subquery? I use only 1 table for this specific query. I need to first get the prodid and qty of ordid = 365 and only then look for the values and i can't find any shortcut here.. — liron29
– liron29, Commented Apr 13, 2021 at 18:35

Gordon Linoff · Accepted Answer · 2021-04-13 18:13:46Z

4

Use exists. SQL Server doesn't support tuples:

WHERE EXISTS (SELECT 1
              FROM item i2
              WHERE i2.prodid = item.prodid AND i2.qty = item.qty AND
                    i2.ordid = 365
             )

answered Apr 13, 2021 at 18:13

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

T.S. Over a year ago

Theoretically, it is possible to concatenate the fields in where and select but that would be big hit on performance. The way question is asked, I don't think subquery is even needed

T.S. · Accepted Answer · 2021-04-13 18:45:42Z

1

The join with inline view should also be a valid approach

SELECT t1.ordid, t1.prodid, t1.qty
FROM item t1 inner join 
     (select prodid, qty FROM item WHERE ordid=365 AND productCategory="Electronics") t2
         on t1.prodid = t2.prodid and t1.qty = t2.qty

answered Apr 13, 2021 at 18:45

T.S.

19.6k11 gold badges70 silver badges96 bronze badges

Comments

Charlieface · Accepted Answer · 2021-04-13 20:00:38Z

1

There is one way you can use the tuple syntax, more correctly called a row-value comparer. It's a bit more messy than ANSI-SQL, but still OK:

SELECT 
    ordid, prodid, qty
FROM 
    item i
WHERE EXISTS (SELECT i.prodid, i.qty
              INTERSECT
              SELECT i2.prodid, i2.qty
              FROM item i2
              WHERE i2.ordid = 365
             )

It looks odd, but it's actually very useful for comparing nullable columns, because INTERSECT compares NULLs as equal.

answered Apr 13, 2021 at 20:00

Charlieface

78.7k8 gold badges35 silver badges77 bronze badges

Collectives™ on Stack Overflow

SQL Server : multiple column subquery

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related