Multiple similar relationships? (1 Viewer)

[CedarTree]

Hello. Let's say I have a table of colors (red, white, blue, etc.) and another table that has People's names, their favorite color, and their least favorite. I can't seem to set up TWO (or more) relationships between the table of colors to the Fav field and the table of colors and the Least Fav field. In Access, I believe you can do this (?) and Access would append _1 and _2, etc. to the tables in the Relationships diagram. But how to do this in SQL server? I keep getting a "you may have cascading issues" or some such warning in SQL server. Thanks!!!

 

[CedarTree]

Hello - any suggestions on this item please? Thanks!

 

[Pat Hartman]

I don't know how you are building the relationship. If you are using the diagram view and dragging one field to the other table, the interface makes an intelligent guess as to what fields you are trying to connect and it suggests a name. It always suggests the "same" name so you need to modify the name in the dialog so that each relationship ends up with a unique name.

 

[adhoustonj]

Can you post table relationship diagram? if you don't have relationships setup already - no problem - just drag all the tables into the relationship view, expand all tables so we can see necessary fields, and then post the screen shot?

 

[Minty]

If you are building a query in T-SQL you would simply use an alias, exactly the same as Access does.
If you are trying to establish a table level constraint with SQL then I would want to see what you have tried?

Bear in mind that multiple constraints can make adding new records troublesome if you don't have all the data available?

 

[adhoustonj]

If it is just least and favorite color and you were storing the ID with peoples names, then you could have:

SELECT tblPeopleNames.full_name, tblColors1.Color as 'Favorite Color', tblColors2.Color as 'Least Favorite Color'
FROM tblPeopleNames
LEFT JOIN tblColors tblColors1 on tblColors1.color_id = tblPeoplesNames.FavColorID
LEFT JOIN tblColors tblColors2 on tblColors2.color_id = tblPeoplesName.LeastFavColorID

If people were ranking colors, then you'd need a table in between tblPeopleNames and tblColors that stored how each person ranked each color... so say tblPeopleColors, and it could store the tblPeopleNames ID for who it was, and then the tblColors ID for what color, and then you could select min max of rankings for each person.

 

[CedarTree]

If you are building a query in T-SQL you would simply use an alias, exactly the same as Access does.
If you are trying to establish a table level constraint with SQL then I would want to see what you have tried?

Bear in mind that multiple constraints can make adding new records troublesome if you don't have all the data available?

Yes, thanks. This would be an alias issue in SQL server. But can I create aliases just using the GUI of sql server or do I need to use T-SQL? Thanks.

 

[CedarTree]

I don't know how you are building the relationship. If you are using the diagram view and dragging one field to the other table, the interface makes an intelligent guess as to what fields you are trying to connect and it suggests a name. It always suggests the "same" name so you need to modify the name in the dialog so that each relationship ends up with a unique name.

I'm trying to use the somewhat usable GUI of sql server - Access GUI is much easier but I'm building the back-end in sql server.

 

[Pat Hartman]

A join is not a relationship @adhoustonj

A relationship is used to implement Referential Integrity.

@CedarTree if you build the diagram, you can drag and drop the way you can using the Access GUI.

 

[Minty]

Yes, thanks. This would be an alias issue in SQL server. But can I create aliases just using the GUI of sql server or do I need to use T-SQL? Thanks.

I tend to write my queries in SQL Server directly in text, as oddly enough I find it simpler...
A simple example - you have a data table that references the same employee table twice to reference a manager and a engineer:

SELECT t1.ModelNo, t1.SerialNo, emp1.FullName as ManagerName, emp2.FullName as EngineerName
FROM tblJobRecords as t1
LEFT JOIN tblEmployees as Emp1 On Emp1.EmpID = t1.ManagerID
LEFT JOIN tblEmployees as Emp2 On Emp2.EmpID = t1.EngineerID

If you use the SQL designer, (from memory) simply keep adding the the table you need multiple times and it will automatically give it an Alias.

 

[Pat Hartman]

I refer you back to the actual question. A join is not a relationship.

@CedarTree Did you create the diagram and use the drag method to create the relationship? I suggested that two days and 7 posts ago!!! I know it works.

 

[CedarTree]

Yes I tried the drag method with no luck... it's okay for now based on other DB design items.

 

[Pat Hartman]

Yes I tried the drag method with no luck...

did you change the name of the relationship to make it unique? The dialog opens with the name of the existing relationship.