Left outer join in crystal report 11

[ckreds]

If im using Left Join to join 2 tables( table_A and table_B)

In my record selection formula editor, i have this criteria {table_A.EmployeeID} = {?empid}

my question is, if i enter an employee id that does not match the data in table_A, does this linking return any value? or can i still display value for a particular field in table_B?

[Lugh]

A left join specifically will show everything in table A, and any matching records in table B. When you restrict table A to showing no records, then there is no way to have matching records in table B. So, no, there will be no values in table B.

You may want to look into full outer joins. That may give you what you are looking for. Not all databases support them, however.

Also keep in mind that Crystal does not really do conditional joins natively. As a general rule, if you want to do fancy joins, you're better off creating a view in your database, and basing the report off of that.

[ckreds]

Thks. but how if Table_A has data but table_B has no data?

[henky@nok.co.id]

Thks. but how if Table_A has data but table_B has no data?
That's left outer join.

[ckreds]

i mean do i get any data returned? or can i get value from a particular field in Table_B? thks.

[henky@nok.co.id]

i mean do i get any data returned? or can i get value from a particular field in Table_B? thks.
Sorry, i still can't figure out what you mean.
but how if Table_A has data but table_B has no data?
If Table_A has data, then you will get some matching records on Table_B.
If Table_A has no data, then you will not get any records on Table_B.

[Lugh]

i mean do i get any data returned? or can i get value from a particular field in Table_B? thks.

Er, if Table_B has no data, what kind of value are you hoping to get returned? You can certainly reference any field in Table_B. But, if there are no matching records, the value will simply be NULL.

[ckreds]

Er, if Table_B has no data, what kind of value are you hoping to get returned? You can certainly reference any field in Table_B. But, if there are no matching records, the value will simply be NULL.


Sorry Lugh, it should be any record return from the linking? Thks

[Lugh]

OK, let's try this with some sample data

Table A
EmpID EmpData
1 Jones
2 Smith
3 Wilson
4 Young

Table B
EmpID OtherData
1 Marketing
2 Engineering
5 Marketing
6 Janitorial

Ok, now. If you do a left join of A to B, your result should look like:


A.EmpID A.EmpData B.EmpID B.OtherData
1 Jones 1 Marketing
2 Smith 2 Engineering
3 Wilson NULL NULL
4 Young NULL NULL


If you restrict your criteria to A.EmpID = 1, then B.OtherData = Marketing.

If you restrict your criteria to A.EmpID = 4, then B.OtherData = NULL.

If you restrict your criteria to A.EmpID = 5, then B.OtherData = NULL (because, even though there is data for B.EmpID = 5, that's not part of the query).


Now, what kind of data, using the above model, do you want to try and get?

[ckreds]

OK, let's try this with some sample data

Table A
EmpID EmpData
1 Jones
2 Smith
3 Wilson
4 Young

Table B
EmpID OtherData
1 Marketing
2 Engineering
5 Marketing
6 Janitorial

Ok, now. If you do a left join of A to B, your result should look like:


A.EmpID A.EmpData B.EmpID B.OtherData
1 Jones 1 Marketing
2 Smith 2 Engineering
3 Wilson NULL NULL
4 Young NULL NULL


If you restrict your criteria to A.EmpID = 1, then B.OtherData = Marketing.

If you restrict your criteria to A.EmpID = 4, then B.OtherData = NULL.

If you restrict your criteria to A.EmpID = 5, then B.OtherData = NULL (because, even though there is data for B.EmpID = 5, that's not part of the query).


Now, what kind of data, using the above model, do you want to try and get?

Thks a lot Lugh, clear enough. But how if table B has no data at all? does crystal report left outer join still return any data?

[Lugh]

Yes. The point of a left join is that you return all the records from table A, regardless what is in table B. Table B could have a bunch of matching records, no matching records, or no records at all. It doesn't matter.