Multiple columns using IN()

[dhookom]

Is there a syntax for using IN () with multiple columns?
For instance:

CREATE TABLE [ColorSizeQuantity](
	[Color] [nchar](10) NOT NULL,
	[Size] [nchar](10) NOT NULL,
	[Quantity] [int] NOT NULL
) ON [PRIMARY]

GO
INSERT [ColorSizeQuantity] ([Color], [Size], [Quantity]) VALUES (N'Red       ', N'L         ', 10)
GO
INSERT [ColorSizeQuantity] ([Color], [Size], [Quantity]) VALUES (N'Red       ', N'XL        ', 5)
GO
INSERT [ColorSizeQuantity] ([Color], [Size], [Quantity]) VALUES (N'Red       ', N'S         ', 8)
GO
INSERT [ColorSizeQuantity] ([Color], [Size], [Quantity]) VALUES (N'Blue      ', N'S         ', 3)
GO
INSERT [ColorSizeQuantity] ([Color], [Size], [Quantity]) VALUES (N'Blue      ', N'M         ', 5)
GO
INSERT [ColorSizeQuantity] ([Color], [Size], [Quantity]) VALUES (N'Blue      ', N'XXL       ', 10)
GO
INSERT [ColorSizeQuantity] ([Color], [Size], [Quantity]) VALUES (N'White     ', N'S         ', 0)
GO
INSERT [ColorSizeQuantity] ([Color], [Size], [Quantity]) VALUES (N'White     ', N'XS        ', 3)
GO
INSERT [ColorSizeQuantity] ([Color], [Size], [Quantity]) VALUES (N'White     ', N'L         ', 20)
GO

What I want is quantities of Red Small, Blue XXL, and White XL. I can do this with a CTE like the following but is there an easier solution?

WITH 
cteCombinations as
(SELECT 'Red' mColor, 'S' mSize 
UNION SELECT 'Blue', 'XXL'
UNION SELECT 'White', 'XL'
)
SELECT mColor, mSize, IsNull(Quantity ,0) mQty
FROM ColorSizeQuantity
RIGHT JOIN cteCombinations ON mColor = Color AND mSize = Size

[pre]mColor mSize mQty
Blue XXL 10
Red S 8
White XL 0[/pre]


Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[Olaf Doschke]

I see, you use this cteCombinations as filter of your data, to just see those color/size combinations of interest.

Well, that is already more elegent in my oppinion than an IN () filter.

To compare tuples with the IN clause you'd need to concatenate, eg [tt]WHERE Color+','+Size in ('Red,S', 'Blue,XXL','White,XL')[/tt].

I introduced a comma to be unambiguous, expecting no comma in either Color or Size values. Watch out: You may also need to trim, if Color and Size are no varchar fields.

But is that really more elegant? Googling I find your solution as a proposed solution and I like it better, as it's much cleaner about the data. Concatenation always will need to convert all the single ladies to the string type and if you ever have a tuple of data containing dates or any other non string type converted with locale settings in effect, you may get wrong results simply because of that and PMS of the server.

Bye, Olaf.

 

[dhookom]

Thanks Olaf. This was what I had expected and had already considered the concatenation but didn't care for it based on the reasons you posted. Concatenation might work well for smaller numbers of records and simple strings.

I had also considered:

SELECT Color, Size, Quantity
FROM ColorSizeQuantity
WHERE (Color = 'Red' and Size = 'S') OR (Color = 'Blue' and Size = 'XXL') OR (Color = 'White' and Size = 'XL')

I understand ORACLE supports a syntax like:

SELECT * 
FROM ColorSizeQuantity 
WHERE (Color,Size) in ('Blue','XXL'), ('Red','S'), ('White','XL')

I was hoping transact SQL might have something similar.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[Olaf Doschke]

Sidenote: MySQL offers it this way:

http://www.sql-workbench.net/comparison/tuple_comparison.html

WHERE (firstname, lastname) IN [highlight #FCE94F]([/highlight] ('Arthur', 'Dent'), ('Ford','Prefect') [highlight #FCE94F])[/highlight]

I don't see T-SQL supporting that, though.

Bye, Olaf.

 

[kjv1611]

What about a series of UNIONed statements?

SELECT Color, Size, Quantity
FROM ColorSizeQuantity
WHERE (Color = 'Red' and Size = 'S')
UNION
SELECT Color, Size, Quantity
FROM ColorSizeQuantity
WHERE (Color = 'Blue' and Size = 'XXL')
UNION
SELECT Color, Size, Quantity
FROM ColorSizeQuantity
WHERE (Color = 'White' and Size = 'XL')

UNIONed statements will run faster than OR or IN statements anyway. Takes more space in your script but runs faster. Just remember to leave any ORDER BY clause out until the very end.


"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57