Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2003
    Posts
    12

    Question Unanswered: Problems creating "such" query

    Hi guys,

    There is a problem now. I'm currently using Access2000, two tables A & B respectively. The both tables contain one columns of data. What I am needed to do now is a query that results the difference in the data.

    Eg. Table A contains 1,2,3 | Table B contains 1,3,5

    The similar numbers: 1, 3
    The different numbers: 2, 5

    My query results need to show 2 & 5 as they are not in both the tables.

    Can anyone enlighten me on this? I've tried Unmatch Query Wizard. But my results only shows 2 but not 5 because the left join only applies on one table - A.

    Thanks for help in advance! Hope to hear from you guys real soon. This is a urgent problem. = )
    There no other place better than this... http://wenz.alexlab.com

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Here are 2. One that uses outer joins and the other uses a combination of union, minus and intersect.

    1)
    select a.a
    from A LEFT OUTER JOIN B on a.a = b.b
    where b.b is null
    union
    select b.b
    from A RIGHT OUTER JOIN B on a.a = b.b
    where a.a is null;

    2)
    (union)
    minus
    (intersection*)

    * - difference in other DBMS's
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Sep 2003
    Posts
    12
    hey, thanks r123456

    i really got what i wan, but that was a trial for me, may i know is it possible for the query to work if there are ten tables?

    same thing applies, i need to get all the different numbers from
    table - a,b,c,d,e,f,g,h,i and j.

    Is it possible? can help me on this? thanks a lot anyway!
    There no other place better than this... http://wenz.alexlab.com

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You're talking about an EXTREMELY intensive query there. Is it necessary to do this in SQL?

    Essentially you need to ask access to compare A to B, A to C, A to D, A to E, A to F, B to C, B to D, B to E, C to E, C to D, E to D

    etc etc

    This will be obscene to do in SQL, I would highly recommend scripting a small function to do it for you, depending on how you intend to use the data.
    Last edited by Teddy; 12-10-03 at 10:28.

  5. #5
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Your comment ->
    "i need to get all the different numbers from
    table - a,b,c,d,e,f,g,h,i and j."

    I have assumed you wish to return a single set containing all numbers from accross all tables that do not appear in the intersection of all tables.

    If this is the case then for more than say 2-3 tables I would personally write a recursive code block. Essentially for a given number of tables any value in table1 that does not appear in table2 can be immediately disregarded as it instantly fails the intersection.

    Thus instead of constructing a 10-way join or 10-way union minus 10-way intersection accross all tables, by using a recursive procedure, one is able to compare a continously decreasing set of tuples with each remaining table, providing the majority of numbers do not form the intersection.

    Example
    ----------

    Table 1 - Table 2 returns a set of 6 matching tuples. Now these 2 tables can be disregarded, and only the 6 resulting tuples need be continued. After comparing to the next table, only 4 rows are returned. As the procedure continues this set decreases in size.
    Last edited by r123456; 12-10-03 at 12:50.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  6. #6
    Join Date
    Sep 2003
    Posts
    12
    But can i know how do i go about doing a recursive code block? can i run it using a macro? i'm really new to Access2000, hope u guys will help me. i'm willing to try though.

    r123456,
    i understand what you are trying to say, basically, compare table 1 & 2 first, then get the different numbers from these two tables, then get the result to compare with other tables am i right?

    regards,
    wensheng
    There no other place better than this... http://wenz.alexlab.com

  7. #7
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    As you are using Access not SQL Server I don't know to what extent Access supports these types of queries.

    For example a typical example of recursion may be:

    List all parts and subparts along with all their prices required to make a specific device. Ie. Device requires 5 main parts and each main part requires 10-20 sub parts etc. This query can not be written in pure SQL hence external code is required, on ORACLE this can be PL/SQL. SQL Server would probably contain a similar concept.

    Access however, I could not tell you. If it does not, then the above queries can be modified. The outer join needs to be altered sligtly whereas the (union) / (intersect) can simply be duplicated. Consider the following,

    TableA Table B
    --------- -----------
    1 1
    3 3
    5 4

    The intersection can be constructed as an INNER JOIN instead (note, intersection compares all columns not just a candidate key, which in this example is suitable), giving the first 2 rows. An obvious next step

    would be:
    select (a, b)
    from a, b
    where (a, b) NOT IN (the intersection returned set), this however would return a set of unwanted combinations that remained from the CP.

    To resolve this one of the following can be done,
    1) Union the tables instead of a cartesian product to eliminate combinations, then where union column NOT IN any of the columns returned by the inner join.
    2) Perform an inner join on all tables with the join condition being the collection of 10 NOT IN / NOT EXISTS operators to check their column with any column in the intersection.

    Clearly the 2nd option is not an option. Regarding option 1, the (minus and intersection) clauses have essentially been re-written. The question can now be simplified.

    Is it faster to use (minus & intersect) as opposed to where NOT IN (INNER JOIN). I would suggest INTERSECT.
    Last edited by r123456; 12-11-03 at 00:08.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  8. #8
    Join Date
    Sep 2003
    Posts
    12
    Hi r1234567 again, thanks for replying promptly.. thanks a lot. appreciated. but somehow i tried something strange.. i think this kind of stupid idea can only come from my brain, wanna take a look? just see below Query Statement:

    select [A].[Object ID], [A].[Object Description]
    from A LEFT OUTER JOIN B on [A].[Object ID]=[B].[Object ID]
    where [B].[Object ID] is null;

    UNION select [B].[Object ID], [B].[Object Description]
    from A RIGHT OUTER JOIN B on [A].[Object ID] = [B].[Object ID]
    where [A].[Object ID] is null;

    UNION select [A].[Object ID], [A].[Object Description]
    from A LEFT OUTER JOIN C on [A].[Object ID]=[C].[Object ID]
    where [C].[Object ID] is null;

    UNION select [C].[Object ID], [C].[Object Description]
    from A RIGHT OUTER JOIN C on [A].[Object ID] = [C].[Object ID]
    where [A].[Object ID] is null;

    UNION select [B].[Object ID], [B].[Object Description]
    from B LEFT OUTER JOIN C on [B].[Object ID]=[C].[Object ID]
    where [C].[Object ID] is null;

    UNION select [C].[Object ID], [C].[Object Description]
    from B RIGHT OUTER JOIN C on [B].[Object ID] = [C].[Object ID]
    where [B].[Object ID] is null;

    Somehow, this gives the result of what i want man! thanks a lot! i really appreciate u guys for helping.. especially r1234567! really.
    There no other place better than this... http://wenz.alexlab.com

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    That is a valid solution for only three tables, please be cautioned that for each table you add, the number of select statements you will need to include will grow exponentially.

  10. #10
    Join Date
    Sep 2003
    Posts
    12
    Hi Teddy,

    i know the number of sql statements are going to grow exponentially.
    However the few solutions other than sql queries i ain't sure. I'm not a programmer or what, that's why i dun understand.

    I'm sorry. I did this Union Queries till the 7th table. when i reach 8th table, there was an error, " Query too complex! "

    So i stopped at 7th table. i think that should be enough. = )
    There no other place better than this... http://wenz.alexlab.com

  11. #11
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I'm not up to speed on my vb lately as I've been working primarily in delphi lately, but I can outline a basic concept. I would do this using an array and a for loop. Essentially declare an array to store your final value list. Open your first data set with SELECT * FROM A and scroll through the record set to assign values to the array...

    Essentially you would need to declare an array, assign the first table to the array, then compare it to the next table. Scroll through each record in the next table, if a match is found in the array, delete the matching field in the array, otherwise add the field to the array from the dataset. Then compare to the next table and do the same.

    basically you'd have a for loop that would iterate and either append or remove depending on a match. I hope that makes any sort of sense heh..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •