What's the best way to write this query?

Question

Post reply

What's the best way to write this query?

Viewing 5 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247183 More actions · Answer 1

Your OrderRules table should be normalised.

OrderRulesId, AccountId, AllowedState

(One row per allowed state, per account.)

(State could, and probably should, also be normalised.)

With this in place, everything becomes much easier.

This reply was modified 6 years, 5 months ago by Phil Parkin.

DesNorton SSC-Insane Points: 24619 More actions · Answer 2

If you are unable to change the structure of your DB, you could use a splitter function. In SQL 2016, you have a built-in splitter function which I believe should work in this case.

The example below is untested, as I do not have access to an instance of SQL 2016 at the moment.

SELECT *
FROM Orders o
INNER JOIN OrderRules r ON r.AccountID = o.AccountID
INNER JOIN STRING_SPLIT(r.AllowedStates, ',') ss ON o.ShipToState = ss.value;

How to post data/code on a forum to get the best help.

Thom A SSC Guru Points: 99143 More actions · Answer 3

I suspect that your SQL actually fails before it gets to the IN. OR is a reserved keyword in T-SQL so you'd first get an error at your alias choice for OrderRules.

Phil is right now, you need to normalise your data. SQL Server does interpret o.ShipToState IN or.AllowedStates as o.ShipToState IN ('NJ', 'PA', 'NY', 'MD') it'll interpret it as o.ShipToState IN 'NJ, PA, NY, MD', which might as well be o.ShipToState - 'NJ, PA, NY, MD'.

Also, instead of an IN, you'll probably want to use as EXISTS instead. So your query (after your data is normalised) ends up with a WHERE clause like:

WHERE EXISTS (SELECT 1
              FROM OrderRules R
              WHERE o.ShipToState = R.AllowedStates)

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

DesNorton SSC-Insane Points: 24619 More actions · Answer 4

DesNorton wrote:

If you are unable to change the structure of your DB, you could use a splitter function. In SQL 2016, you have a built-in splitter function which I believe should work in this case. The example below is untested, as I do not have access to an instance of SQL 2016 at the moment.
SELECT *
FROM Orders o
INNER JOIN OrderRules r ON r.AccountID = o.AccountID
INNER JOIN STRING_SPLIT(r.AllowedStates, ',') ss ON o.ShipToState = ss.value;

The above sample will most likely produce duplicate records, which can be eliminated by DISTINCT or GROUP BY.

You could also use EXISTS as mentioned by Thom.

SELECT o.*
FROM Orders o
INNER JOIN OrderRules r ON r.AccountID = o.AccountID
WHERE EXISTS (SELECT 1 FROM STRING_SPLIT(r.AllowedStates, ',') ss
              WHERE o.ShipToState = ss.value);

How to post data/code on a forum to get the best help.

Goalie35 SSC Eights! Points: 871 More actions · Answer 5

Thanks for everyone's advice. I ultimately went with Phil's idea of normalizing the data. I was initially concerned with the number of records this would produce in the OrderRules table, due to listing out each state, but it sounds like that's the better option to go with.

Thanks again!