Full Outer and joining other tables 2

[wolrah]

I have two table want to join. Non of the rows in table A are in table B and non of the rows in table B are in A (thus using a Full Outer join). Need to report values from A and B on the same report line. At the same time, a key field on both A and B is used to join a third table C to get common data. The problem is I have to join C to either table A or B. If I join to A, then when B records show up I don't get the values from B on my report.

I can make it all work if I use a SQL command object because I can put an 'or' and join C to both A and B using the 'or'. Because of strange reasons I won't go into here I been told I can use the SQL command object - must use the Crystal Reports interface to join table al let CR create the SQL. So far unsuccessfull.

Here is the from clause I built for the SQL command but can't get CR to recreate this. The 'got cha' is the 'or' on the front of the sixth line from the bottom. CR don't seem to want to do this:

  from (
	select SOCIAL_SECURITY_NUM, mbe.Plan_Id, 
		Annual_Employee_PreTax_Cost,
                Annual_Employer_PreTax_Cost, 
		Employer_PreTax_Cost, 
		Company_Tax_ID, YearID, calendar_name, 
		DEPCODE, Employee_PreTax_Cost
	  from member_benefit_elections mbe
		) mbe [COLOR=blue]full outer join[/color] (
	select SOCIAL_SECURITY_NUM, Plan_Id, 
		Annual_Employee_PreTax_Cost,
                Annual_Employer_PreTax_Cost, 
		Employer_PreTax_Cost,  
		Company_Tax_ID, YearID, calendar_name, 
		DEPCODE, Employee_PreTax_Cost
	  from member_benefit_elections_changes
	) mbec
    		on mbe.social_security_num = 
                   mbec.social_security_num
   		and mbe.company_tax_id = 
                    mbec.company_tax_id 
		and mbe.plan_id = mbec.plan_id 
  INNER JOIN qry_employee_only qeo 
   ON (mbe.SOCIAL_SECURITY_NUM = qeo.SOCIAL_SECURITY_NUM 
  AND mbe.DEPCODE = qeo.DEPCODE 
  AND mbe.Company_Tax_ID = qeo.Company_Tax_ID)
   [COLOR=red]or[/color] (mbec.SOCIAL_SECURITY_NUM = qeo.SOCIAL_SECURITY_NUm
  AND mbec.DEPCODE = qeo.DEPCODE 
  AND mbec.Company_Tax_ID = qeo.Company_Tax_ID)
  INNER JOIN qryRptCompany cm 
   ON qeo.Company_Tax_ID = cm.Company_Tax_ID 
  AND qeo.Group_Tax_Id = cm.Group_Tax_ID

 

[jmd0252]

Well I would use the table c as the primary table, then join table a and table b to the c, using left outers. That way the common data would be what you could group off of.

 

[wolrah]

Just verified that won't work. Example:

tableC tableA tableB
1 1 2
2 3 4
3 5 6
4
5
6

There is never a key match on all 3 tables so no rows are returned.

 

[lbass]

This really is a situation where you should use a command. You could set it up like this:

select A.field1, C.field1
from A inner join C
union all
select B.field1, C.field1
from B inner join C

This avoids the extra records created by using an 'or' statement.

-LB

 

[Turkbear]

Hi,
As jmd0252 noted, be sure you are using a LEFT OUTER JOIN - that way table c's records will be returned even if no a and b match exists. Also, you cannot have any criteria applied to a or b since that will convert the join into an EQUI join.




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

 

[wolrah]

I had a typo in my original post. Don't know if you picked up on it or not but in my phrase "I been told I can use the SQL command" the word "can" should be "can't".

What I want is all the rows in tables A and B, not all the rows in C. I only need rows from C when they match one in A or B.

Can CR do subqueries without using a sql command?

 

[lbass]

I understood what you meant. The command I showed would do exactly what you describe. I would argue for using the command.

If you can't, you could also handle this by using A and C in a main report, and B and C in a subreport. You just wouldn't be able to sort or group them together.

-LB