×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Relationship

Relationship

Relationship

(OP)
1.How do you find a relation between two colomns in the same table?
2.How do you find a relation between two colomns in the two different tables?

is there any sql to determine this?

pls reply

thanks

RE: Relationship

those are very deep metaphysical, to say nothing of epistemological or philosophical, questions

what's the context?

r937.com | rudy.ca

RE: Relationship

r937, That's an awsome answer, I don't think it's quite the direction that the op is looking for.

123,
Typically there will be a PK/FK relationship defined between the primary key of the column to the forign key.  This will be normally be done if the relationship is on one table or two tables.

The code to see this information will vary depending on database platform.  Based on your name I assume that you are using Microsoft SQL Server.  On SQL Server this code will give you all the constraints in the current database.

CODE

SELECT *
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible.  All it takes is a little research. (Me)
noevil
http://www.mrdenny.com

RE: Relationship

Key Columns not constraints sorry.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible.  All it takes is a little research. (Me)
noevil
http://www.mrdenny.com

RE: Relationship

mrdenny, indeed they are very deep questions

here's a sample table:

create table mrdenny
( col1 integer not null primary key
, col2 varchar(99)
, col3 varchar(99)
);

now, can you tell me how you would find a relation between col2 and col3 in this table?

me, i would first of all do a GROUP BY on them and examine the results

what if the results of such a GROUP BY included the following:

Toronto, IA
Toronto, KS
Toronto, OH
Toronto, ON
Toronto, SD

what is the relationship between col2 and col3 now?

how about their relationships to col1, the PK?


see?  context matters

r937.com | rudy.ca

RE: Relationship

Oh course context matters. I guess I broke my own rule.  I made assumptions as to what direction the op was going without clarifying first.  Consider my hand slapped.

Op, can you please clarify.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible.  All it takes is a little research. (Me)
noevil
http://www.mrdenny.com

RE: Relationship

1. There should be no relationship between 2 columns in the same table. If there is any, the structure might be wrong. In any case, I don't think any database engine has any clue about such relationships.

2. Information_Schema might be of help...

HTH

pipe
Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

RE: Relationship

Sorry but I had to pipe in with my .02 :)

/pedantry on
The poster's question wasn't about relationships, it was about relations. Now, I'm fairly certain it was intended to be a question about relationships but none of the subsequent posts asked for the clarification.

A relation is, loosely speaking, the physical implementation of the entity, the 'table'. When speaking of a relation, we speak of how each column in the 'table' is somehow related to the entity being modeled. Hence, a 'customer' entity or table can have name, account number, and address which all relate to the customer.

A relationship is different in that it describes how different tables in a database are related to each other. Typically this is through the use of common keys between the tables but can also exist in a non-key relationship. Examples would be relationships between salary and bonus structures where the bonus is set but based on a range in the salary table.

/pedantry off

RE: Relationship

Quote (danvlas):

1. There should be no relationship between 2 columns in the same table.

totally disagree  

what if one of 'em's the primary key?

isn't that what the rules of normalization are based on?  the relationship (dependent, functionally dependent, independent) of non-key attributes on (all or part of) the primary key?

r937.com | rudy.ca

RE: Relationship

Quote (danvlas):


1. There should be no relationship between 2 columns in the same table.

What if you have an employee table that stores the employee's supervisor?
Employee
EmpID
FName
LName
SupervisorID (Fk to EmpID)

now I have a relationship between EmpID and SupervisorID in the same table.  Not a problem!

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

RE: Relationship

Rudy, you have a point.
However, I (stubborn me) stick with the idea that no column depends on another column. Otherwise changing one value would imply altering the value in the other.
Primary key is indeed an exception, but all columns/column combination depend on it. Not because any value in the record needs to be changed when PK value changes, but because the identity of the record is different.

Leslie, I also disagree with your example.
The employee is the same no matter who his supervisor is. Changing the name as a result of marriage does not change the supervisor. On the other hand, a new supervisor will not change any other attribute of the employee.
What you gave as an example shows the case of a table that acts as 2 different relations.
Moreover, the structure you presented loses all subordination history (how many supervisors an employee had, who were they and when).
The foreign key SupervisorID in this table is just a little better than a boolean HasSupervisor field.
A 'Supervision' table would solve history, but it would remove the SupervisorId FK from Employees table:

supID (PK)
supEmployeeID (FK to employees)
supSupervisorID (FK to employees)
supDateSet

Of course, a constraint on the last 3 fields would be necessary.
Needlessto say, removing the field from the table means removing the self-join, thus the 'relationship' between 2 fields in the same table.

pipe
Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

RE: Relationship

dan, it is not a question of changing a value, but rather, if you pick a different value for the one column, does that give you a different value of the other column

the classic example:

employees
empid deptid deptname

here the pk is empid, and there is a relationship between deptid and deptname  

r937.com | rudy.ca

RE: Relationship

Which combination should not exist in the employees table...
DeptName should be in Departments, PK DepID, referenced by the foreign key in Employees

pipe
Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

RE: Relationship

yes, but dan, that's only if the tables are properly normalized, which was my point!!

what if they aren't?  neutral

what if the purpose of asking the question in post #1 of this thread was "how do you detect a relationship between two columns?"

if you don't believe me, go back and read that first post

me, i'm not going to say very much more about this besides context matters and wait for the original poster, who probably didn't understand half the replies in this thread, to come back and clarify for us

r937.com | rudy.ca

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close