Skip to Content

Query Help

I am hoping someone can help me understand why I am not getting the correct SQL join.

I have the following set up:

Table_1 Outer Join Table_2 1,n

Table_2 Outer Join Table_3 n,1

Table_2 Outer Join Table_4 n,1

Table_2 Outer Join Table_5 n,1

When I go an build a query choosing 1 field from tables 1, 3, 4, and 5. I would expect all of the tables to be joined like the following:

Table_1 LEFT OUTER JOIN Table_2

LEFT OUTER JOIN Table_3

LEFT OUTER JOIN Table_4

LEFT OUTER JOIN Table_5


Instead I am getting the following:

Table_3 RIGHT OUTER JOIN Table_2

LEFT OUTER JOIN Table_5

RIGHT OUTER JOIN Table_1

LEFT OUTER JOIN Table_4


Because of this RIGHT, LEFT, RIGHT, LEFT I am not getting all of the data I would expect on my report. I have tried altering the cardinality, but that has not helped me any.


This is coming from the Information Design Tool 4.1 SP6.


Thanks


Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

3 Answers

  • avatar image
    Former Member
    Oct 07, 2015 at 09:35 PM

    Hi Jeff,

    Can you please provide the snapshot of the universe that you have created by applying the joins.

    I think there is a problem with the outer join conditions on universe.

    Also provide the data in tables and output you wanted.

    So that it could be understood easily.

    Thanks,

    Swapnil

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 08, 2015 at 08:51 PM

    Is there not a way or setting that will enforce only left joins?

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 09, 2015 at 05:53 PM

    Hi Jeff,

    Try creating a database view which solves the relation

    Table_1 LEFT OUTER JOIN Table_2

    Add this new table to the universe. Create there the corresponding outer joins from the VIEW to the other tables 3, 4 and 5. Create a context grouping the view and these 3 tables and modify your query to use the view's fields instead of table_1 and table_2 fields.

    Please let us if the new generated SQL left joins the view to T3, to T4 or to T5. Or if it builds SQL with one right join to the view and two left joins to the other lookup tables, for example ...


    T3 right join VIEW

    left join T4

    left join T5

    If that's the case you'll have the result you want.

    Best regards,

    Fernando

    Add comment
    10|10000 characters needed characters exceeded