Join problem in Designer

[DrSmyth]

I have a problem relating to the join between two tables. Basically one of two fields in table is connected to a field in the secound table.

I always want both sets of results to occur in a report so can i just manually add the two joins and leave it or do i have to add a context.

I've not used context before as i've always managed to get around it with aliases, although i do have some information on context if this is required (Unfortunately it's in the form of a 100 page guide which i haven't even started to read yet)

Any ideas???

 

[blom0344]

Can you be more specific about this? What do you mean with 'both sets of results' to appear in the report.
Or by manually add 2 joins?

Do you want to have more than one type of relationship between the two tables?

Generally one would go for a context or alias if the SQL no longer properly fetches data in case of a fan or chasm trap...

T. Blom
Information analyst
tbl@shimano-eu.com

 

[DrSmyth]

OK... Table 1 has fields y and z of which either can connect to field a in table 2.

By both sets of results i mean that i want all items in table 2 where field a equals either field y or z in table 1.

The types of relationships are both the same (One to Many, outer join). Manually adding, i mean inserting a join via designer as opposed to getting BO to detect the join.

 

[blom0344]

Yes, that is exactly what I mean with more than one type of relationship between the 2 tables , isn't it?
You cannot resolve this in one 'simple' SQL statement. Technically , a union query should be able to pull it of, cause in the where part you can manually change the joins between tables. So , its either manually changing the union query (in query panel, with the 'do not generate SQL'option)

or:

using 2 dataproviders, either using 2 contexts or aliases

Got a feeling the union way will give you just the single dataset you want ...............

T. Blom
Information analyst
tbl@shimano-eu.com

 

[DrSmyth]

Cheers T!!!