Identifying a one to many relation
Identifying a one to many relation
(OP)
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:
Query
select
sh.salesid,
sh.custaccount,
sl.salesid,
sl.linenumber
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?
Regards,
Tomas
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:
Query
select
sh.salesid,
sh.custaccount,
sl.salesid,
sl.linenumber
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?
Regards,
Tomas
Talk To Other Members
RE: Identifying a one to many relation
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
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
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