#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    3
    Rep Power
    0

    Query runs out of Memory


    Hi I am running the following query:

    SELECT
    route_list_sb032206."Route_ID",
    route_list_sb032206."StartDate",
    route_list_sb032206."EndDate",
    route_list_sb032206."Carrier_ID",
    route_list_sb032206."SB_Carrier_Name",
    route_list_032206."Date",
    route_list_032206."Route_ID",
    route_list_032206."Carrier_ID",
    route_list_032206."Carrier_Name",
    route_list_032206."Occupant_Addr",
    route_list_032206."Occupant_City",
    route_list_032206."State",
    route_list_032206."Zip"
    FROM

    public."route_list_sb032206", public."route_list_032206"

    Where route_list_sb032206."Route_ID" != route_list_032206."Route_ID"


    and getting the following error:

    out of memory for query result

    ********** Error **********


    I know I am running this wrong so let me look to those smarter than I.

    What I am trying to accomplish is that the Route_ID columns on both tables contain the same data but one table has 4 more IDs (rows) than the other column and 50 that are different (not in both) when I run an looking for matches - Inner Join (that query runs fine) . I am trying to get a result set of what those different route ID's are.

    Any help would be most appreciated.

    Sean
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    14
    Rep Power
    0
    Doing an inner join where something is not equal is a bad idea. This will return exponentially more matches than you're intending.

    A better approach could be to do an inner join where they ARE equal, and then select a result set where the Route_ID is not in the inner join result set.

    Example:

    SELECT "Route_ID"
    FROM public."route_list_sb032206"
    WHERE "Route_ID" NOT IN
    (SELECT route_list_sb032206."Route_ID",
    FROM public."route_list_sb032206", public."route_list_032206"
    Where route_list_sb032206."Route_ID" = route_list_032206."Route_ID") x1
    UNION
    SELECT "Route_ID"
    FROM public."route_list_032206"
    WHERE "Route_ID" NOT IN
    (SELECT route_list_sb032206."Route_ID",
    FROM public."route_list_sb032206", public."route_list_032206"
    Where route_list_sb032206."Route_ID" = route_list_032206."Route_ID") x2;

    This should return a set of Route_ID's that are only in one table or the other. All matches will be excluded due to the NOT IN.

IMN logo majestic logo threadwatch logo seochat tools logo