| Author |
Message |
bowja
Newbie
Joined: 07 Dec 2009
Location: Australia
Online Status: Offline
Posts: 31
|
   Topic: Link foriegn keys to a multiple column primary key
Posted: 31 Jul 2013 at 2:20pm |
|
Hello,
I am hoping someone can assist with my table join query.
I have three tables event, business and location. The location table is set up with a multiple column primary key of category and location_no i.e.
location_category|location_no|location_description
business|1|ANZ
business|2|IOOF
business|3|PWC
business|4|POLICE
business|5|LOCAL
event|1|CONVENTION
event|2|ETSTADIUM
event|3|WHARF
event|4|COMMUNITY
The event and business tables contain a location_no field which needs to join to the location table where the location_category is event and business respectively.
I have attempted this by creating alias tables however the result displayed is linked to the first instance of the location_no. I do not know how to incorporate the location_category column.
NB: I link the tables using the link tab in Database Expert so perhaps it is possible to link it more effectively via and SQL expression? ( I haven't used this area previously)
Any assistance would be greatly appreciated
|
|
If you think you can or think you can't you are right - Paraphrased quote Henry Ford
|
 IP Logged |
|
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 02 Aug 2013 at 4:57am |
|
I am not sure if you need to worry about the primary key...
I would just use the link tab to connect the tables as they should be linked, and not worry about the tables as defined in the database. Let CR do the work, they way you want it to.
Primary keys on a table just ensure no duplication, and a faster retrieval rate from the database.
HTH
|
|
IP Logged |
|
bowja
Newbie
Joined: 07 Dec 2009
Location: Australia
Online Status: Offline
Posts: 31
|
Posted: 05 Aug 2013 at 11:03am |
|
Hi HTH,
I really appreciate your reply.
Unfortunately crystal reports does not recognise which of the rows with the matching key should be displayed (it just takes the first.
For example after linking the event.location_no field location.location_no.
If a record has a 3 in event.location_no field and I display it with location.location_description then I see "PWC".
I want to see "WHARF" as the "WHARF" row in the location table has "event" in the location_category table and 3 in the location_no table.
Just letting the crystal "do the work" links the table based on the field from each table joined. This is not enough as the location_category comes into play too.
Currently my poor work around has been to split the table content into two formula fields. i.e. if event.location_no = 1 then "Convention" else if
event.location_no = 2 then "IOOF"......
This isn't satisfactory as it requires maintenance and could easily become out of date.
|
|
If you think you can or think you can't you are right - Paraphrased quote Henry Ford
|
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 05 Aug 2013 at 11:12am |
|
I'm not sure that i understand. Are you letting CR do the linking for you? I have found that CR will either link incorrectly or will link on more fields that it should, so I usually erase all links. Then I put the links that I know are correct.
you can also apply criteria to filter out records that are not part of what you are looking for as well as group by a field.
From the description, it sounds that the linking is allowing records to duplicate, and inside of the duplication is this value that you are looking at.
The simple test would be to write the sql that CR is using to create the report and see what the results are in Query Analyzer or some other SQL tool. It could be that CR is doing what it was told to do...
HTH
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 05 Aug 2013 at 11:17am |
if I understand your issue you have to add the location table multiple times to tghe report (using alias) and outer join each one to the correct fields in the location table. It may try to auto-link but as lockwelle indicates, you can remove those and make the joins that you need.
you can then creaet one fomrual field concantenatig all of the possible fields reults (from each alias table) into one string.
Edited by DBlank - 05 Aug 2013 at 11:18am
|
|
IP Logged |
|
bowja
Newbie
Joined: 07 Dec 2009
Location: Australia
Online Status: Offline
Posts: 31
|
Posted: 20 Aug 2013 at 10:30pm |
|
Hi DBlank,
Thanks for your reply. Unfortunately I don't think anyone has fully understoop my issue yet (probably due to my explanation and the weird table set up).
The event table only contains 1 usefull column (location_no) this is the same for the business table. There is NO column in either of these tables which has the corresponding location_category of business or event. This is what is causing my issue! Unfortunately I cannot change the db so I need to add logic.
I have set up alias tables but as the event and business tables I am linking them to don't have a field to say "business" or "event" respectively there are no joins I can make other than business.location_no > location.location_no for the first pairing and event.location_no > location.location_no for the second
What I need to incorporate is some logic for each of the separate pairs i.e. LOGIC: where location.location_category = "business" for the first join (JOIN: business.location_no = location.location_no).
And alternatively: LOGIC: location.location_category = "event" and JOIN: event.location_no = location.location_no
for the second join.
I guessing that that this needs to be coded somehow/somewhere as Database expert only allows fields to be joined (no additional logic can be added).
Fingers crossed this clarifies my question.....
|
|
If you think you can or think you can't you are right - Paraphrased quote Henry Ford
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 21 Aug 2013 at 4:01am |
i think I understand your issue now.
What do you mean by you 'set up an alias table'?
If you can create a view or stored proc as your source this woud be the eaisest way. You can add an 'and category=xxx' to each of the joins.
Otherwise consider using a crystal Command which allows you to write a SQL statement wher you can include your condition on the join.
|
|
IP Logged |
|
bowja
Newbie
Joined: 07 Dec 2009
Location: Australia
Online Status: Offline
Posts: 31
|
Posted: 21 Aug 2013 at 4:24pm |
|
Thanks again DBlank.
By alias i just mean i added the table twice in the database expert. The second instance is the alias and is called location_1.
I have not used either of the methods you described before so will investigate these today.
Thank you for your suggestions.
|
|
If you think you can or think you can't you are right - Paraphrased quote Henry Ford
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 22 Aug 2013 at 3:55am |
|
Teh alias wont do much for you (assuming I am understanding the problem corerctly). adding a conditon on the join should work.
|
|
IP Logged |
|
bowja
Newbie
Joined: 07 Dec 2009
Location: Australia
Online Status: Offline
Posts: 31
|
Posted: 09 Feb 2014 at 12:08pm |
|
Hi everyone,
I thought I would post the solution to this as although it turned out to be simple I wasn't a part of CR I was familiar with. Sorry it has taken me so long to close it off. Thanks to both DBlank and lockwelle for your help.
DBlank the solution was as you stated I used a Crystal Command with Select, From and Where.
Kind Regards,
|
|
If you think you can or think you can't you are right - Paraphrased quote Henry Ford
|
|
IP Logged |
|
|
|
