Help with linking 3 tables

[Former Member]

Hi,

I'm having trouble with a report. I would like to show all 'Deployed' and 'Auto-Added' Computers of all 'Active' Organizations.

I have 3 Tables: Computers, Organizational_Units, Status

When I just use Organization and Satus this is the SQL query it shows:

SELECT "Status"."Status", "Organizational_Units"."Name"

FROM "TEB-AlloyNavigatorDB6"."dbo"."Organizational_Units" "Organizational_Units" INNER JOIN "TEB-AlloyNavigatorDB6"."dbo"."Status" "Status" ON "Organizational_Units"."Status_ID"="Status"."ID"

WHERE "Status"."Status"=N'Active'

When I add Computers, the report is empty. The SQL is:

SELECT "Status"."Status", "Organizational_Units"."Name", "Computers"."Computer_Name"

FROM ("TEB-AlloyNavigatorDB6"."dbo"."Computers" "Computers" INNER JOIN "TEB-AlloyNavigatorDB6"."dbo"."Organizational_Units" "Organizational_Units" ON "Computers"."Organization_ID"="Organizational_Units"."ID") INNER JOIN "TEB-AlloyNavigatorDB6"."dbo"."Status" "Status" ON ("Computers"."Status_ID"="Status"."ID") AND ("Organizational_Units"."Status_ID"="Status"."ID")

WHERE "Status"."Status"=N'Active'

Can anyone help me with this? To me it seems like Status is linked in a wrong way??

Edited by: B. Kleer on Sep 8, 2011 3:02 PM

[Former Member · Accepted solution]

Hi Kleer,

Please check the following Query and try to create a crystal report with the following query

SELECT "Status"."Status", "Organizational_Units"."Name", "Computers"."Computer_Name"

FROM ("TEB-AlloyNavigatorDB6"."dbo"."Computers" "Computers" INNER JOIN "TEB-AlloyNavigatorDB6"."dbo"."Organizational_Units" "Organizational_Units" ON "Computers"."Organization_ID"="Organizational_Units"."ID") INNER JOIN "TEB-AlloyNavigatorDB6"."dbo"."Status" "Status" ON ("Computers"."Status_ID"="Status"."ID")

WHERE "Status"."Status"=N'Active'

Thanks,

Naga.

[Former Member · Accepted solution]

Hi Nagabrahmam,

Thank you for your answer.

When I put in the SQL you suggested in the SQL expression editor, I get this error: sql state: 42000 native error: 156.

[Former Member]

If you have built your report through Crystal's database editor, can you post your links here? I suspect your need to modify your link for status to an outer join.

Debi

[Former Member]

Hi kleer,

Thanks for your response. I suggested you, create a new crystal report by using the query through ADD Command.

SELECT "Status"."Status", "Organizational_Units"."Name", "Computers"."Computer_Name"

FROM ("TEB-AlloyNavigatorDB6"."dbo"."Computers" "Computers" INNER JOIN "TEB-AlloyNavigatorDB6"."dbo"."Organizational_Units" "Organizational_Units" ON "Computers"."Organization_ID"="Organizational_Units"."ID") INNER JOIN "TEB-AlloyNavigatorDB6"."dbo"."Status" "Status" ON ("Computers"."Status_ID"="Status"."ID")

WHERE "Status"."Status"=N'Active'

Thanks,

Naga.