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 |
|
KevinN
Starting Member
2 Posts |
 Posted - 2010-04-02 : 09:01:12
|
| Hi all,I am working on a sql query using Microsoft sql server 2005 for the first time for a school project and having trouble with a column that can have multiple values.I have two tables, Consultant and Specailty.Consultant has columns, Cid (Primary key), CName, hiredate, Salary and specialty(Foreign Key)Specialty has columns Billing and specialty (Primary key)As you can see, Consultant is joined to Specialty with specialty. I have inserted all the necessary values into specialty i.e. 'Analyst','170' etc. What I need to do is give some Consultants multiple specialties. I know that i can place a Cid in the Specialty table and remove specialty from Consultant to get the results i need. But i have to follow a specific logical diagram. Any suggestions will helpThank you in advance,Kevin |
|
|
byrdzeye
Starting Member
14 Posts |
Posted - 2010-04-02 : 09:28:14
|
| Your solution is not the best way.Hint: You need a another table. |
 |
|
|
KevinN
Starting Member
2 Posts |
Posted - 2010-04-02 : 09:31:21
|
| I considered a table that holds Cid and specialty. However I have to follow a specific diagram that dictates the tables as I described, this is a project where I must create a database based off a erwin diagram supplied to me. |
|
|
|
byrdzeye
Starting Member
14 Posts |
Posted - 2010-04-02 : 10:39:24
|
| Creating a column with multiple values is SUCH a bad idea that you prolly won't get any help with it.If your er diagram has specialty as a fk in the consultant, table then a consultant can only have 1 specialty. |
|
|
|
MrQuizzles
Starting Member
20 Posts |
Posted - 2010-04-02 : 16:02:50
|
| Are you absolutely forced to use that diagram to dictate the structure of the database? 'Cause what byrdzeye is saying is right. This is not the best way to do it. To create a basic one-to-many relationship, you have two options: One involves 3 tables and one involves 2.The one involving 3 has consultants, specialties and then a table to relate the two. Your second post mentions that you thought about exactly this.The one involving 2 has cid as a foreign key in specialties (the opposite of what you have now) and a unique record for each specialty that each consultant has.Both solutions have their merits, and whether to use one or the other largely depends on what sort of data you'll be putting into them. It hinges on how redundant or unique the many side of the relationship will be. In your case, it seems the 3-table solution would be best, but your diagram apparently doesn't let you do that, so I don't know what to say : /If it lets you, go for the 2-table solution. Just flip the relationship you have set up now. It won't be optimal, but it'll be better than having a column holding multiple values. |
|
|
|
|
|
|
|
|
SQL Server 2005. Inserting multiple values