Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible. This is minimal polite behavior on SQL forums. Now we have do your job for you! >> I have created a Parent [sic] table (User_Accounts) and I have two child [sic] tables (User_Skills and User_Locations) << The correct terms are Referenced and referencing tables in RDBMS. The ASCII pictures you did post are wrong. You believe Kabbalah magic! That is how you can have a magical “id” that changes from a user to a skill to a location, to a squid to an automobile, etc. CREATE TABLE Users (user_id CHAR(10) NOT NULL PRIMARY KEY, first_name VARCHAR(20) NOT NULL, last_name VARCHAR(20) NOT NULL); Did you notice that there is a key? Since it is an identifier, it is a string; we use numeric data for computations. That the name columns are post office standards? CREATE TABLE User_Skills (dot_code CHAR(9) NOT NULL, user_id CHAR(10) NOT NULL REFERENCES Users(user_id), PRIMARY KEY (dot_code, user_id)); The DOT codes are the dictionary of occupational titles. This is the industry standard (http://www.occupationalinfo.org/ ). We do not want or need that silly Kabbalah number! CREATE TABLE User_Locations (san CHAR(10) NOT NULL, user_id CHAR(10) NOT NULL REFERENCES Users(user_id), PRIMARY KEY (san, user_id)); There is also a Standard Address Number (SAN) in many industries and we have UPS, FedEx and other address system. If this is a physical location, then use HTM numbers. >> What I want is to return a record [sic: rows are not records] set that has searches against the child [sic] tables. However there is no limit to the amount of search criteria for the skill and locations as long as it matches any of those it should return the users first/last name and user_id. >> In C# I've tried to use TVP (which is selected using a checkboxlist) to send through the array of DOTs and SANs but I am not sure what I do with it now? << This is SQL, so we do not care about C# or any other presentation layers. 1) The dangerous, slow kludge is to use dynamic SQL and admit that any random future user is a better programmer than you are. It is used by Newbies who do not understand SQL or even what a compiled language is. A string is a string; it is a scalar value like any other parameter; it is not code. Again, this is not just an SQL problem; this is a basic misunderstanding of programming principles. 2) Passing a list of parameters to a stored procedure can be done by putting them into a string with a separator. This also stinks; for the details, read: http://www.simple-talk.com/sql/learn-sql-server/values()-and-long-parameter-lists/ http://www.simple-talk.com/sql/learn-sql-server/values()-and-long-parameter-lists---part-ii/ Do this with a long parameter list. You can pass up to 2000+ parameters in T-SQL, which is more than you probably will ever need. The compiler will do all that error checking that the query version and the procedural code simply do not have unless you write a full parser with the standard error codes. You can now pass local variables to your procedure; you can pass other data types and get automatic conversions, etc. In short, this is just good software engineering.
--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
No comments:
Post a Comment