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

Identifying a one to many relation

Identifying a one to many relation

Identifying a one to many relation

If i write a query based on a one to many relationship, how do I know from looking at the output that its based on a 1 to many relationship? Would appreciate your feedback.

For Example:

SalesHeader Table

SalesID CustAccount Product
1       123         21801
2       456         50001
3       789         22550

SalesLine Table
SalesID   LineNumber   CustAccount   Product    Amount  Qty
1         1            123           21801      100     2
1         2            123           21801      200     4
2         1            456           50001      100     2
3         1            789           22550      150     1
3         2            789           22550      100     3

if I write a query to join these 2 table, I would have the following output:

from salesheader sh
join salesline sl
on sh.salesid = sl.salesid

sh.salesid  sh.custaccount sl.salesid  sl.linenumber
1           123            1           1            
1           123            1           2
2           456            2           1
3           789            3           1
3           789            3           2

How from looking at this output can i see that it is based on a one to many relationship?

Do I need to look at what fields I used to join the  tables with.

I have used a simple example so would really appreciate your comments.

THe same applies to:
1 to 1
many to many

Are there tools/apps that can be used to identify the relationshio types?


RE: Identifying a one to many relation

you really cannot definitively tell anything from looking at data smile

if you have access to the database in order to run queries, then you can probably run queries against the INFORMATION_SCHEMA database as well

that can easily tell you what the relationships are, by identifying the foreign keys

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

RE: Identifying a one to many relation

Frankly, if two tables in a database are related, it's almost always a many-to-one relationship. You can't have a many-to-many realtionship between tables (you have to split it out into two many-to-ones); and in 20 years of working with and designing databases, I've only ever seen one one-to-one (and that was for disk space reasons in the olden days when disk was expensive).

The way you would detect what's going on is to examine your joins against the schema. If you're joining a foreign key on table A to the primary key of table B, it's a many-to-one, with A as the many and B as the one.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

RE: Identifying a one to many relation

Well frankly this is something you should know from the content of the data before you look at any of the records. For instance orders to order details is clearly a one to many relationship, an order can have many individual line items.

If you want to know if a child table has multiple records for any FK, then this will work:

Select count(*), FK_ID from childtable group by FK_ID having count(*)>1

If any records are returned, you have a one to many relationship or a many to many relationship.

Generally a many to many relationship will consist of a table that contains two FKS related to two differnt tables.
Again thsi is usally obvious if you understand what the table is for. Students and courses are related in a many to many relationship, one course can have many students, one student can take many courses. Usually these relationships are shown in a join table that countains the PK from students and the PK from courses and nothing else.

One-to-one relationships are best enforced by using the parent PK as the PK in the child table. So if USERID is the PK of both tables (and the FK in the child tables as well), likely it is a 1-1 relationship. The query above will return zero results of course.  

"NOTHING is more important in a database than integrity." ESquared

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