Urban planning community

+ Reply to thread
Results 1 to 3 of 3

Thread: SQL or Crystal exclusion query statement

  1. #1
    Cyburbian TerraSapient's avatar
    Registered
    Nov 2009
    Location
    The Glass City
    Posts
    2,610

    SQL or Crystal exclusion query statement

    Shot in the dark, but anyone out there familiar with querying SQL or Crystal databases that can help me write a statement to select records in one table that are not associated to another table?

    Details:
    Table 1 is an asset record table
    Table 2 is a work routine table
    I need to select all assets in Table 1 that do not have values in Table 2.

    I have been trying null statements, but haven't had any luck.
    I already have established the link between the tables in crystal, and I have run a report that shows me all assets that are included in a work routine, but I cannot get the ones that are not included in a work routine.

    Words of wisdom appreciated.
    Occupy Your Brain!

  2. #2
    Cyburbian Linda_D's avatar
    Registered
    Nov 2006
    Location
    Jamestown, New York
    Posts
    1,635
    In SQL, try NOT EXISTS or NOT IN ...

    STACKOVERFLOW.COM gives a good explanation of both and why NOT EXISTS is probably better ... NOT IN vs NOT EXISTS
    If a free society cannot help the many who are poor, it cannot save the few who are rich. -- John F. Kennedy, January 20, 1961

  3. #3
    Cyburbian TerraSapient's avatar
    Registered
    Nov 2009
    Location
    The Glass City
    Posts
    2,610
    I tried a left outer join between table 1 and table 2 with
    {table1.record} isnull {table2.record}

    But I kept getting an error message stating that table 2 wasn't part of the equation.

    For some reason crystal doesn't support not exist or not in.

    Thanks for the tip though Linda_D! I will put that one in my pocket for later.

    Any other ideas?


    Edit: I got it! For the record, if anyone in the future is trying to do this you use the select expert and run the same statement I have above, but like this:
    {table1.record}
    isnull ({table2.record})
    Last edited by TerraSapient; 30 Oct 2012 at 3:45 PM.
    Occupy Your Brain!

+ Reply to thread

More at Cyburbia

  1. Replies: 5
    Last post: 10 Jun 2011, 10:19 AM
  2. Replies: 3
    Last post: 28 Oct 2009, 8:59 AM
  3. Replies: 2
    Last post: 09 Feb 2009, 4:54 PM
  4. Anderson, IN Crystal Arch
    Design, Space, and Place
    Replies: 6
    Last post: 09 Aug 2004, 4:48 PM
  5. Crystal Palace
    Design, Space, and Place
    Replies: 2
    Last post: 21 Oct 1997, 8:48 AM