Cardinality - joining on 2 fields

[thedon_1]

I have seen joined queries where the link has been made between 2 fields e.g

Query 1 Country to Query 2 Country AND
Query 1 City to Query 2 City

Why is this? I'd guess it's a replacement to making a Country&City key and then doing a single join on that but i'm not sure.

Could anybody give me some info on why this would be done?

Thanks

[blom0344]

To make absolutely sure that in case multiple cities exist - with the same name - in different countries would mess up the results from the joined tables?

[CognosPaul]

That's still a poor join. What happens with locations like Greenville, USA or Richmond, Canada?

With Greenville, even joining on the state level won't help, as there are at least 3 in New York alone.

[thedon_1]

Paul, I just made the fields up to show what I meant but yeah in reality that would not be the best join option.

Blom0344, if I had the Country England and City London, and I joined this with the Country Canada, City London, right now I would make a country&city key so I would get a correct join. If I used 2 joins, on City and Country, this would give me the same result, correct?

Is there any advantage/ disadvantage of using one method over the other?

Thanks

[blom0344]

That's still a poor join. What happens with locations like Greenville, USA or Richmond, Canada?

With Greenville, even joining on the state level won't help, as there are at least 3 in New York alone.

Hmm, that may be true, but I tried to make a point about WHY such a join would be defined. If a true key would be absent then the Greenvilles would surely be identified by adding the county to the name  , like  'Greenville (Orange county)' or perhaps a postal code or whatever (rendering them unique)

Applying joins over strings is usually a matter of bad design. I expect similar performance over 2 seperate fields compared to a concatenated set (caný back it though  :)   )

[absriram]

Blom0344,

When users create data items that they use, it's tedious to create a key in each query and then join them based on the key. If this were a data warehouse then you should be worry about best practice and good designs. Bug, these are report level joins. Cognos gives you the capability to join based on multiple columns, but that doesn't mean you have to join on multiple columns.

Hope that helps.
Sriram.
http://www.cognosonsteroids.com

[blom0344]

Thanks, but I do not need your help.   ;)

There are cases where joining (be it in a model or within the report) will not result in  1:1 / 1:n / n:1 cardinality , causing wrong results. Adding another field to the join definition may correct this.

I do not understand your remark about the creation of keys.  If keys are available they are the preferred way of joining. If they are not available , there is no way to 'produce' them from a user's perspective.

Using 'in-report' query joins demands a very good understanding of the underlying datamodel. I have witnessed hilarious results with users that were clueless and simply thought using the same name was adequate for each and every join