StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Since storing multiple values in a single column like you are with SubTypes is almost always a bad idea (Needing to do <code>LIKE '%;2;%' AND LIKE '%;3;%'</code> is a huge red flag), if I've understood your requirements correctly this looks like a many to many relationship, which usually involves a cross table to link the two entities, in this case Vehicles and Subtypes.</p> <p>If you removed SubTypeIDs from your Vehicle table, and created a link table Vehicle_Subtypes</p> <pre><code>VehicleID SubTypeID 1 2 1 3 </code></pre> <p>You would then be able to write queries to get the appropriate SubTypes for a given vehicle, something like</p> <pre><code>SELECT S.SubType FROM SubTypes S INNER JOIN Vehicle_Subtypes X ON X.SubTypeID = S.SubTypeID WHERE X.VehicleID = @VehicleId </code></pre> <p>May need more logic in there to account for TypeID but this seems like the right design. </p> <p>EDIT: I take it all back. Misunderstood the part about going from SubTypes to Vehicles, had it backwards in my head. Going the other way is more difficult, finding records that meet a variable number of conditions can be tricky. Given the same link table, (assuming you have a lot of control over the SQL that gets generated) you could write the somewhat hackish query</p> <pre><code>SELECT VehicleId FROM Vehicle_Subtypes WHERE SubTypeId IN (1, 2) GROUP BY VehicleId HAVING COUNT(*) = 2 </code></pre> <p>You would have to ensure <code>COUNT(*) =</code> number of subtypes selected, this way the vehicle has all of them. Still, it feels like there should be a better way, I'll keep thinking on it.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload