Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cjohns38
Starting Member
1 Post |
 Posted - 2007-11-19 : 20:37:03
|
*************************Background information*************************I’ve built a database for our studies at work. I have restructured the database into four basic tables. The table “Group� keeps track of the differet types of groupings of variables. Table “Lookup� provides descriptions for different options within a group. Table “Study� provides information about the study including a study number (not the same as the primary key). The “Study� table also functions as a “master page� within the program. Table “Question Details� provides information regarding questions asked in each study and functions like a detial page. The idea is that I can add items to just about any of the tables easily and have things generate on the fly. The program allows me to add multiple reponses within the details variables (Comp1 through Comp3) resulting in items which are commas separated values. These comma separated values correspond to LookupID’s from the Lookup table and the corresponding detials associated with it. *****************************Table Information*****************************Table: GroupGroupID: Priamry Key (AutoNumber)Group: TextTable: LookupLookupID: Primary Key (AutoNumber)GroupID: NumberDescription: Text Table: StudyStudyID: PrimaryKey (AutoNumber)Study Number: (Number)Table QuestionDetails: StudyID: PrimaryKey (Number) QuestionID: PrimaryKey (AutoNumber)Comp1: TextComp2: TextComp3: TextQuestion: Text*****************Problem*****************The problem is really due to the Comp1 through Comp3 being set as Text rather then numeric values. Because they are set to numeric values I am unable to create a standard join between the two fields do to a type mismatch (Numeric vs. Text). Thus when I try and create a query that pulls all the information from Table QustionDetails along with StudyNumber from table Study I end up with no results. What makes things a little more complicated is that I need to have the other two tables joined in the query as well so I can return the text for Group and the Description. Does anyone know a way to join a numeric variable and a CSV variable so that you can query it and return text values? Does this even make any sense? �Any help would be appreciated. Thanks!PS - I have a demo access example if it would help anyone... |
|
|
georgev
Posting Yak Master
122 Posts |
Posted - 2007-11-20 : 06:32:06
|
I suggest you rethink your design.This looks like a many-to-many relation which you have tried to treat as a 1-to-many.The correct model for a many-to-many is to create an interim/intermediate table which a compound primary key of the two foreign key references.E.g. many consultants can be assigned many projects - you would model this as follows:Projects(projectID, description ...)Consultants(consultantID, firstName, lastName ...)ConsultantProjects(consultantID, projectID)So instead of suggesting a sticking plaster solution (aka a bodge), I suggest you do it right :)
George<3Engaged! |
 |
|
|
|
|
|
CSV join problem