Linking two tables with a left join

[Flupke]

Hello all!

I want a report, based on two tables. In table two, there are different records who are linked to records in table one. In table one, there are also different other records who stand on their own and have no relation with any record in table two.
I want a list of ALL the records in table one and also the related records in table two for each record in table one if they exist.
In SQL this can be achieved by a LEFT JOIN so that ALL the records in table one are displayed and not only those who have relations in table two.
I don't seem to be able to realise that in Crystal reports. I only see the result of an inner join and not those of a left join.
I also tried to give a SQL-statement with a LEFT JOIN as data-source for the report, but the result was the same (that of an inner join).

Can someone help me with that?

Many thanks and greetings from Brugge (Bruges - Belgium),

Michel

 

[Turkbear]

Hi,
Depending on the CR version, on the database tab, there should be an option for the Link expert..Right-Click the link line between the 2 tables, and choose Left-Outer.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Flupke]

I've tried that, and it does not change one bit. It does not work in CR 8.5 nor in CR XI. Left-outer nor right-outer. Even with a SQL-statement as source for the report, in which a left join or a right join is used, I do not get the desired results.

 

[kskid]

I halso have CR 8.5 and CR 11 and they BOTH work fine here.

I am guessing you have a select criteria against table 2, which will automatically treat table 2 as an equal join.

-lw

 

[Turkbear]

Hi,
I am not sure what could be the issue..I use it all the time in 8.5,10,and XI ( no longer use 8.5 but it did work)

Which database and connectivity type are you using?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[smcnulty2000]

Post the sql statement. That might give us a clue. Tell what kind of server (oracle, ms-sql, mysql, ...) as well.

Include, if you can, some info about the data in the two tables and what the data is like at the joined fields. Especially tell us about any nulls that are in the data at the joined fields. i.e., if the record where the join is occurring would be joining on a null with your two extra records.

Scott.