Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2008
    Posts
    29

    Unanswered: Complex SQL query

    Hi all,

    I've built an SQL query that I would like to cross-check.

    The following tables are used:
    - DImport_1 (e_id*, f_id*, type1_id*, val1_1, val1_2)
    - DImport_2 (e_id*, f_id*, type2_id*, val2_1, val2_2)
    - C (c_id*, f_id, some other fields)
    - CxE (c_id*, e_id*)
    - R (r_id*, c_id, type1_id, type2_id, some other fields)

    Tables DImport_{i} are initialized with all distinct possible combinations of (e_id, f_id, type{i}_id) obtained by INNER JOINing C, CxE and R. Each key has two values (val{i}_1, val{i}_2) which are initially NULL.

    A procedure imports some data from an external system and updates the values of val{i}_1 and val{i}_2 where applicable, leaving "un-imported" values to NULL.

    The goal of the query is to create a dataset of the form:

    DInput (r_id*, e_id*, res1, res2)

    where val{i} are calculated from val{i}_1 and val{i}_2
    and only when val1_1 or val2_1 is not NULL.

    This is the SQL I've built :
    Code:
    SELECT R.r_id,
           CxE.e_id,
           CalcType1(d1.val1_1, d1.val1_2) AS res1,
           CalcTYpe2(d2.val2_1, d2.val2_2) AS res2
    FROM   (((DImport_1 AS d1
              INNER JOIN (DImport_2 AS d2
                          INNER JOIN CxE
                            ON d2.e_id = CxE.e_id)
                ON d1.e_id = CxE.e_id)
             INNER JOIN C
               ON (d2.f_id = C.f_id)
                  AND (d1.f_id = C.f_id)
                  AND (CxE.c_id = C.c_id))
            INNER JOIN R
               ON (R.type1_id = d1.type1_id)
                  AND (R.type2_id = d2.type2_id)
                  AND (R.c_id = C.c_id))
    WHERE  NOT (d1.val1_1 IS NULL AND d2.val2_1 IS NULL);
    Thanks for any comments.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    and the problem is?
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2008
    Posts
    29
    Just wanted to check that this was consistent and wouldn't give unexpected results.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    well is it consistent
    doesit give unexpected results
    is there a problem
    do you feel there is a issue
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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