Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Jul 2009
    Posts
    12

    Unanswered: 3 table outer join problem

    I have 3 tables (I'll call them A, B and C for now). Each has a primary key ID and table B has an additional PK called POS

    A is my primary table and has an amount column (SubTotal)
    B is a many to 1 table relating to A (i.e. A.ID = B.ID)

    Within table B is a column (we'll call it CSEQ here). The key to table C is A.ID+B.CSEQ (i.e. concatenated)

    Table C has an amount column I wish to SUM (TranAmount)

    There are more A rows than there are matching C rows (i.e. not every A.ID appears in table C)

    In pseudo SQL terms I want something like this:

    SELECT A.ID FROM A, C WHERE (A.SubTotal - SUM(C.TranAmount)) > 100

    I realise I need some sort of JOIN on A.ID = B.ID and C.ID = (A.ID + B.CSEQ) but my SQL knowledge is pretty basic. Every time I think I have it I'm missing rows or my C.TranAmount column is always null.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by bfp
    In pseudo SQL terms I want something like this:
    in pseudo SQL terms you can do something like this:

    SELECT ...
    FROM A
    LEFT OUTER JOIN B ON B.ID = A.ID
    LEFT OUTER JOIN C ON C.ID = A.ID + B.CSEQ

    alternatively, that last line might have to be

    LEFT OUTER JOIN C ON C.ID = A.ID || B.CSEQ


    further help i cannot give, since your specs are so vague

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2009
    Posts
    12

    3 table outer join problem

    But if I do something as basic as that (which I already tried) I run into a multitude of problems:

    select A.ID, SUM(A.Amount), SUM(isnull(0,C.TranAmount)) FROM
    A
    left outer join B
    ON A.ID = B.ID
    left outer join C
    ON C.ID = (A.ID + B.CSEQ)
    group by A.ID
    order by A.ID

    Firstly, the SUM of A.Amount is multiplied by the number of rows in table B that matches table A (which is obviously wrong as I stated originally, I just want A.Amount). If I don't use the SUM on A.Amount I get an aggregate/GroupBy error (which I "sort of" understand).

    Additionally, using this basic syntax I can't work out how to do a where clause for the value range I'm after.

    I thought I was getting close with one of my earlier attempts:

    SELECT AID FROM
    (SELECT ID as AID, Amount FROM A) A JOIN
    (SELECT A.ID, B.CSEQ FROM A, B WHERE A.ID = B.ID) B ON A.ID = B.ID
    LEFT OUTER JOIN
    (SELECT ID, SUM(isnull(TranAmount,0)) AS Camt FROM C GROUP BY ID) C ON C.ID = (A.ID+B.CSEQ)
    WHERE A.Amount - C.Camt > 100 and A.Amount - C.Camt < 200
    ORDER BY A.Amount - C.Camt, A.ID

    In this example I am at least not duplicating the A.Amount value but I know my ...FROM C group by ID doesn't make sense as I really want to be grouping on A.ID (at least that's my way of thinking) but I couldn't work out the syntax.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by bfp
    But if I do something as basic as that (which I already tried) I run into a multitude of problems:
    then perhaps it's time for you to share the actual details of your actual tables rather than this --

    SELECT A.ID FROM A, C WHERE (A.SubTotal - SUM(C.TranAmount)) > 100

    which merely requires

    ... GROUP BY A.ID, A.SubTotal

    along with the necessary joins


    so this is just another example of a thread where i got lost due to over-simplification

    good luck!!

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2009
    Posts
    12

    3 table outer join problem

    I've made it simple because other than changing the table names I don't believe I've left anything out.

    Here's a script to create/populate/select/drop. The select just needs to be finished. I don't know what else I could be providing as info. I don't understand why grouping by SubTotal makes sense.

    CREATE TABLE A (ID VARCHAR(10), SubTotal DECIMAL(5,2))
    GO
    CREATE TABLE B (ID VARCHAR(10), POS INT, CSEQS VARCHAR(2))
    GO
    CREATE TABLE C (ID VARCHAR(12), TranAmount DECIMAL(5,2))
    GO
    INSERT INTO A VALUES('12345', '100')
    GO
    INSERT INTO A VALUES('12346', '100')
    GO
    INSERT INTO A VALUES('12347', '101')
    GO
    INSERT INTO B VALUES('12345', 1, '01')
    GO
    INSERT INTO B VALUES('12345', 2, '02')
    GO
    INSERT INTO C VALUES('1234501', 50)
    GO
    SELECT A.ID FROM A, C WHERE (A.SubTotal - SUM(C.TranAmount)) > 100
    GROUP BY A.ID, A.SubTotal
    GO
    DROP TABLE A
    GO
    DROP TABLE B
    GO
    DROP TABLE C
    GO

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by bfp
    I don't understand why grouping by SubTotal makes sense.
    if you want it in the SELECT clause of the query, you ~must~ do it, based on ANSI SQL syntax rules (the forum you posted in)

    Quote Originally Posted by bfp
    SELECT A.ID FROM A, C WHERE (A.SubTotal - SUM(C.TranAmount)) > 100
    GROUP BY A.ID, A.SubTotal
    hey, what happened to B?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oh, wait... my bad

    A.Subtotal wasn't in the SELECT clause, it was in the WHERE clause

    well, that's not going to work, it has to be in the HAVING clause because of the GROUP BY

    SELECT A.ID FROM A, C
    GROUP BY A.ID, A.SubTotal
    HAVING (A.SubTotal - SUM(C.TranAmount)) > 100

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jul 2009
    Posts
    12

    3 table outer join problem

    I see where you're coming from but your example over simplifies my problem.

    What happened to B? Well that's (one of) the parts I can't work out. You mentioned "along with the necessary joins". If I knew what they were I (possibly) wouldn't be posting the question in the first place (I realise you're only trying to help...and I appreciate it). Quoting myself "my SQL knowledge is pretty basic".

    Given that:

    A.ID + B.CSEQ = C.ID

    There are As which don't have the above matches (i.e. OUTER JOIN resulting in SUM(SubTotal) = 0).

    Expanding on the above statement, there are A.IDs which don't have matching B.IDs. There are also A.ID + B.CSEQ combinations which don't have matching C.IDs.

    The small example of INSERTs I posted shows this.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, time to stop pussyfooting... what database system is this? or would you like the ANSI SQL version?

    also, for the sample data you provide, what should the query return?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jul 2009
    Posts
    12

    3 table outer join problem

    To stop "pussy footing" as you say...I'm working on a program to translate a query from another environment (non-RDBMS) to SQL.

    In this example it's MSSQL...but ideally I'm looking for ANSI cos it won't always be MSSQL. But I am prepare to use DB specifics if required (as long as there's a solution for MSSQL, DB2, Oracle...maybe MYSQL).

    I realise I over simplified my example. Replace the inserts with this:

    INSERT INTO A VALUES('12345', '150');
    INSERT INTO A VALUES('12346', '200');
    INSERT INTO A VALUES('12347', '101');
    INSERT INTO B VALUES('12345', 1, '01');
    INSERT INTO B VALUES('12345', 1, '02');
    INSERT INTO B VALUES('1234', 1, '01');
    INSERT INTO B VALUES('1234', 1, '02');
    INSERT INTO C VALUES('1234501', 50);
    INSERT INTO C VALUES('123401', 50);
    INSERT INTO C VALUES('123402', 50);

    and change the where clause to be > 100 and < 200.

    I'd expect to see 12347.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by bfp
    In this example it's MSSQL...but ideally I'm looking for ANSI cos it won't always be MSSQL. But I am prepare to use DB specifics if required (as long as there's a solution for MSSQL, DB2, Oracle...maybe MYSQL).
    the database systems you mentioned vary wildly in their implementation of standard SQL concatenation

    let's use the plus sign for now, which MSSQL likes but the others don't (see my remark in post #2 )
    Code:
    SELECT A.ID
         , A.SubTotal
         , B.CSEQS
         , A.ID + B.CSEQS AS myconcat
         , C.ID
         , C.TranAmount
      FROM A
    LEFT OUTER
      JOIN B
        ON B.ID = A.ID
    LEFT OUTER
      JOIN C
        ON C.ID = A.ID + B.CSEQS
    
    ID     SubTotal  CSEQS  myconcat  ID        TranAmount
    ------ --------- -----  --------- --------- -----------
    12345  150.00    01     1234501   1234501   50.00
    12345  150.00    02     1234502   (null)    (null)
    12346  200.00    (null) (null)    (null)    (null)
    12347  101.00    (null) (null)    (null)    (null)
    is this the correct data?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jul 2009
    Posts
    12

    3 table outer join problem

    That data looks correct. So all I need now is grouping on A.ID and producing a SubTotal - SUM(TranAmount) value in a > 100 < 200 expression.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT A.ID
         , A.SubTotal
         , SUM(C.TranAmount) AS sum_trans
      FROM A
    LEFT OUTER
      JOIN B
        ON B.ID = A.ID
    LEFT OUTER
      JOIN C
        ON C.ID = A.ID + B.CSEQS
    GROUP
        BY A.ID
         , A.SubTotal
    HAVING A.SubTotal + COALESCE(SUM(C.TranAmount),0)
           BETWEEN 100 AND 200
    
    ID     SubTotal  sum_trans
    ------ --------- ---------
    12347   101.00   (null)
    12345   150.00   50.00
    12346   200.00   (null)
    Quote Originally Posted by bfp
    I'd expect to see 12347.
    got a couple extra, dintcha
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Jul 2009
    Posts
    12

    3 table outer join problem

    I do believe I said > 100 and < 200 and also it's - not + (which excludes the 100 values)

    However, having said that...you're a champ. I've run this against my full table setup and the results match exactly what I expect.

    Thanks for sticking this one through.

    Now to see if I can produce that programmatically....sigh.
    Last edited by bfp; 07-26-09 at 12:47.

  15. #15
    Join Date
    Jul 2009
    Posts
    12

    3 table outer join problem

    I've had a new spanner thrown into the works. I've need to produce a similar report to:

    SELECT A.ID
    FROM A
    LEFT OUTER
    JOIN B
    ON B.ID = A.ID
    LEFT OUTER
    JOIN C
    ON C.ID = A.ID + B.CSEQS
    GROUP
    BY A.ID
    , A.SubTotal
    HAVING A.SubTotal - COALESCE(SUM(C.TranAmount),0)
    BETWEEN 100 AND 200

    But this time instead of A.SubTotal I need SUM(D.Amount) where D.Amount is a joined table of A.ID = D.ID.

    I tried

    SELECT A.ID
    FROM A
    LEFT OUTER
    JOIN B
    ON B.ID = A.ID
    LEFT OUTER
    JOIN C
    ON C.ID = A.ID + B.CSEQS
    LEFT OUTER
    JOIN D
    ON D.ID = A.ID
    GROUP
    BY A.ID
    HAVING COALESCE(SUM(D.Amount),0) - COALESCE(SUM(C.TranAmount),0)
    BETWEEN 100 AND 200

    but I'm not getting as many rows as I expect. I think I understand why (intersecting on tables C and D?...which I don't want) but - obviously - don't know the solution.

Posting Permissions

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