1:M relationship

[Shin25]

Hi All

I have a report where the data is coming from two data sources with a primary key on both tables being the link. However table 1 pk has 421 records and each record will match table 2 pk, but table 2 might have more than 1 entry per pk. So making it a 1:m relationship.....in database expert I have linked table1 pk to table2 pk and made the relationship left outer join.

I need to know how best to carry out the 1:m relationship in database expert?

Iam using CRxi Rel2.

Many Thanks

 

[smcnulty2000]

Are you sure table 2 is using a primary key ? It might just be a regular index that allows duplicates. A primary key is defined as unique. If it is primary then there should be no duplicates. Foreign key is probably (hopefully) what you mean.

1:M requires no extra work. It works even on an inner join. You use left outer if there is a possibility that a record on the left might not match any records on the right.

For example; if you have a database that has Orders, and each order can have many order lines, then you can just do an inner join. If you can have orders without order lines you would need an outer join.

Mostly the difference between 1:M and 1:1 is for design or conceptual purposes. Crystal won't care. Neither should your database.

Are you trying this and experiencing problems?

 

[Shin25]

thanks for the above....

foreign key sounds more right for table2.

Yep I am experiencing problems as when inner join or LOJ from table1 to table2 I am not getting the correct number or records. Source 1 has 421 records and I am not even getting 421 records back on the above?

Iam more thinking is it in database expert/linking options issue? ie. 'link type'???

 

[smcnulty2000]

What is your record selection formula like?
Any chance you've got an issue there?

Can you get 421 records if you don't have the second table present?

How many records in the second table?

 

[Shin25]

Thanks for the above it was the record selection error.

Many Thanks