INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Multiple columns using IN()

Multiple columns using IN()

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

CODE --> SQL

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?

CODE --> SQL

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 


mColor	mSize	mQty
Blue	XXL	10
Red	S	8
White	XL	0 

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

RE: Multiple columns using IN()

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 WHERE Color+','+Size in ('Red,S', 'Blue,XXL','White,XL').

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.

RE: Multiple columns using IN()

(OP)
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:

CODE --> SQL

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:

CODE --> SQL

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

RE: Multiple columns using IN()

What about a series of UNIONed statements?

CODE

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close