What's the best way to write this query?

Question

Post reply

What's the best way to write this query?

Goalie35

SSC Eights!

Points: 871
More actions
June 4, 2019 at 6:44 pm

#3650223
I have a table of rules for orders. The rules table determines several things but in this example, I need to determine if the state the order was placed from, is allowed to order the item for that particular account. In my query, I want to check an order id against the rules table. Basically, if the order matched against one of our rules, return a result. If not, return no records.
Orders table:
OrderID | AccountID | ShipToName | ShipToState
111 | 123 | John S. | PA
222 | 123 | Amy M. | TX

OrderRules table:
OrderRulesID | AccountID | AllowedStates
1 | 123 | NJ, PA, NY, MD

In the above example, order #111 is allowed to ship because it's from PA. Order #222 however cannot because the state is not one of the allowed states.
So, my query would be something like the following:
```
SELECT
*
FROM
Orders o
INNER JOIN OrderRules or ON or.AccountID = o.AccountID
WHERE
o.ShipToState IN or.AllowedStates (this line doesn't work)
```

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

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

Phil Parkin SSC Guru Points: 246985 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 5 years, 6 months ago by Phil Parkin.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

DesNorton SSC-Insane Points: 24401 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: 99133 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: 24401 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!