Kaseya Community

SQL Query on kasadmin.org & kasadmin.address

This question is answered

Hi!

We want to make a custom report using SQL Reporting Service to have list of all organization and their address (Street, Town ...)

I've tried :

SELECT kasadmin.org.orgName, kasadmin.address.streetline FROM kasadmin.org INNER JOIN kasadmin.address ON org.id = address.connectionFK;
SQL Server report nothing just the two columns street & orgName but blank

then 

SELECT kasadmin.org.orgName, kasadmin.address.streetline FROM kasadmin.org LEFT JOIN kasadmin.address ON org.id = address.connectionFK;

It's report only left table data

SELECT kasadmin.org.orgName, kasadmin.address.streetline FROM kasadmin.org Right JOIN kasadmin.address ON org.id = address.connectionFK;

It's report only right table data

So does anyone have an idea?

Etienne Deneuve
First French KCA

Verified Answer
  • SELECT DISTINCT kasadmin.[vbo_Contact_List].ORGNAME, streetLine, city, zipCode, orgRef, orgType.ref

    FROM kasadmin.[vbo_Contact_List]

    INNER JOIN kasadmin.org ON org.id = vbo_Contact_List.OrgID

    INNER JOIN kasadmin.orgType ON org.orgTypeFK = orgType.id

    ORDER by  orgType.ref, city ASC;

    I've done this just for now, and it does what I want ;)

    Thanks

All Replies
  • I've forget one stuff,

    With SQL Server i've build a UML diagram to look where's the foreign key who link the two tables, this is the org.id = address.connectionFK but if I do :

    Select address.connectionFK from kasadmin.address;

    It's show me null data...

  • Then I found a View named kasadmin.vbo_Organizations_Addresses who is diplayed empty...

    This is a bug or it's normal?

  • I'm working on something similar today so this should fix yours

    SELECT kasadmin.org.orgName, kasadmin.address.streetline FROM kasadmin.org

    INNER JOIN kasadmin.address ON org.id = kasadmin.org.id;

  • SELECT DISTINCT kasadmin.[vbo_Contact_List].ORGNAME, streetLine, city, zipCode, orgRef, orgType.ref

    FROM kasadmin.[vbo_Contact_List]

    INNER JOIN kasadmin.org ON org.id = vbo_Contact_List.OrgID

    INNER JOIN kasadmin.orgType ON org.orgTypeFK = orgType.id

    ORDER by  orgType.ref, city ASC;

    I've done this just for now, and it does what I want ;)

    Thanks