Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2003
    Location
    New York
    Posts
    160

    Unanswered: query from 3 tables

    hi,

    I have 3 tables
    [Credit Memo - Expense Details] - which i refer to CMED
    [Credit Memo - Project Change Request] - which i refer to CMPCR
    [Credit Memo Table] - which i refer to CM

    CM is connected to CMED by 1 field: CM.[Invoice # Credit is applied against (if applicable)] = CMED.[Invoice # / Expenses Reference #])

    CM is connected to CMPCR by 1 field: CM.[PCR # (if applicable)] = CMPCR.[PCR Number]

    CMED and CMPCR are not related


    My goal query: show all the fields in CM (and (if invoice columns match in CMED, show that) or (if pcr number columns match in CMPCR, show that) )

    So if there is only a match with CMPCR the CMED fields should be blank.

    I am thinking of a union but just couldn't get it.

    Below is the code to get all the records where CM invoice matches AND pcr matches
    i need it to be OR

    Code:
    SELECT 
    CM.[Project Name], CM.[Credit Memo #], CM.[Date of Credit Memo Request], CM.[Date of Credit Receipt], CM.[PO Number], CM.[PCR # (if applicable)], CM.[# of Units], CM.[Unit  Cost], CM.[Net Cost], CM.Tax, CM.Freight, CM.[Total Credit], CM.Description, CM.[Credit Applied?], CM.[Invoice # Credit is applied against (if applicable)], CM.Comments, 
    CMPCR.[PCR Number], CMPCR.[DC Number], CMPCR.Category, CMPCR.[Sub-Category], CMPCR.[Building Location / Floor], CMPCR.[Vendor(s) Impacted (if any)], 
    CMED.[Invoice # / Expenses Reference #], CMED.Company, CMED.[DC Number], CMED.[PO Number], CMED.[Cost Center / Billing Code], CMED.[Building Location / Floor], CMED.Category, CMED.[Sub-Category], CMED.Transaction, CMED.[Sub-Transaction]
    FROM
     ([Credit Memo Table] CM INNER JOIN [Credit Memo - Expense Details] CMED ON CM.[Invoice # Credit is applied against (if applicable)] = CMED.[Invoice # / Expenses Reference #]) 
    INNER JOIN [Credit Memo - Project Change Request] CMPCR ON CM.[PCR # (if applicable)] = CMPCR.[PCR Number];
    MS access 2003 - i hate it but i jsut came on board and this is already setup/

    Thanks
    Beyond Limitation

  2. #2
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    how about to use outer join?
    Code:
    SELECT 
    CM.[Project Name], CM.[Credit Memo #], CM.[Date of Credit Memo Request], CM.[Date of Credit Receipt], CM.[PO Number], CM.[PCR # (if applicable)], CM.[# of Units], CM.[Unit  Cost], CM.[Net Cost], CM.Tax, CM.Freight, CM.[Total Credit], CM.Description, CM.[Credit Applied?], CM.[Invoice # Credit is applied against (if applicable)], CM.Comments, 
    CMPCR.[PCR Number], CMPCR.[DC Number], CMPCR.Category, CMPCR.[Sub-Category], CMPCR.[Building Location / Floor], CMPCR.[Vendor(s) Impacted (if any)], 
    CMED.[Invoice # / Expenses Reference #], CMED.Company, CMED.[DC Number], CMED.[PO Number], CMED.[Cost Center / Billing Code], CMED.[Building Location / Floor], CMED.Category, CMED.[Sub-Category], CMED.Transaction, CMED.[Sub-Transaction]
    FROM
     ([Credit Memo Table] CM LEFT OUTER JOIN [Credit Memo - Expense Details] CMED ON CM.[Invoice # Credit is applied against (if applicable)] = CMED.[Invoice # / Expenses Reference #]) 
    LEFT OUTER JOIN [Credit Memo - Project Change Request] CMPCR ON CM.[PCR # (if applicable)] = CMPCR.[PCR Number];

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    On systems not supporting (LEFT) OUTER JOINs, those may indeed be simulated with a UNION.
    The fist part is then the corresponding INNER JOIN, while the second part only interrogates the first table, with an additional WHERE condition "IS NULL" on the join column.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  4. #4
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    you mentioned the database is ms access, I believe there's equivalent of OUTER JOIN in Access. I'm not sure but I think it's LEFT JOIN instead of LEFT OUTER JOIN

  5. #5
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    Quote Originally Posted by Peter.Vanroose
    On systems not supporting (LEFT) OUTER JOINs, those may indeed be simulated with a UNION.
    The fist part is then the corresponding INNER JOIN, while the second part only interrogates the first table, with an additional WHERE condition "IS NULL" on the join column.
    by the way, this solution can't work. idea is OK but you have to use cartesian product instead of inner join. then using combination of AND, OR conditions defined in WHERE clause you'll retrieve what you need without using UNION. but for now forget this solution and try to find equivalent of LEFT OUTER JOIN in your DB server.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i disagree, madafaka

    peter's union suggestion works perfectly, you should try it

    it's also possible to simulate a full outer join with a (suitably coded) union of left and right joins
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    maybe it works but why use UNION and put together 2 or more selects if you can retrieve your date using one select.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    why? because it might be way faster, that's why
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    Quote Originally Posted by r937
    why? because it might be way faster, that's why
    Now you're wrong. The performance is the reason I avoid using UNION.

    "When using the UNION statement, keep in mind that, by default, it performs the equivalent of a SELECT DISTINCT on the final result set. In other words, UNION takes the results of two like recordsets, combines them, and then performs a SELECT DISTINCT in order to eliminate any duplicate rows. This process occurs even if there are no duplicate records in the final recordset."

    "Sometimes you might want to merge two or more sets of data resulting from two or more queries using UNION. For example:"
    Code:
    SELECT column_name1, column_name2
    FROM table_name1
    WHERE column_name1 = some_value
    UNION
    SELECT column_name1, column_name2
    FROM table_name1
    WHERE column_name2 = some_value
    "This same query can be rewritten, like the following example, and when doing so, performance will be boosted:"
    Code:
    SELECT DISTINCT column_name1, column_name2
    FROM table_name1
    WHERE column_name1 = some_value OR column_name2 = some_value
    Last edited by madafaka; 11-04-05 at 07:48.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    dude, we were talking about a UNION to simulate a LEFT OUTER JOIN, versus your suggestion of a cartesian product to simulate a LEFT OUTER JOIN
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    ok, I didn't realise this. but this solutions are silly anyway. I can't immagine how those select statements would look like, if you're joining 3 tables and you can't use OUTER JOIN. I believe there must be some OUTER JOIN equivalent in every standard SQL database.

  12. #12
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Just a reply to some of the topics mentioned in previous posts:

    - Using "UNION ALL" instead of "UNION" avoids the "SELECT DISTINCT" performance overhead; in that case, a "UNION" emulation of an OUTER JOIN is in principle equally performant. (Most of the time, OUTER JOIN will be a bit more performant, since only a single pass has to be made through the left table, but in rare cases the UNION ALL solution may be more performant, especially when lots of rows have no matching row).

    - There are SQL database systems lacking the OUTER JOIN syntax, especially older versions: e.g. Oracle before version 8, DB2 before version 6. Nobody uses these nowadays, but who knows ...

    - Performance is not necessarily boosted when using "OR" instead of "UNION ALL" !
    To the contrary: the two queries in a UNION ALL may use indexes, while as a rule-of-thumb an OR condition never uses indexed access.

    - The equivalent of the query
    Code:
    SELECT a.c1, a.c2, b.c2, b.c3
    FROM tablea AS a LEFT OUTER JOIN tableb AS b ON a.c2 = b.c2
    where a.c2 is a foreign key and b.c2 is the corresponding primary key, is
    Code:
    SELECT a.c1, a.c2, b.c2, b.c3
    FROM tablea AS a, tableb AS b WHERE a.c2 = b.c2
    UNION ALL
    SELECT a.c1, a.c2, NULL, NULL
    FROM tablea AS a WHERE a.c2 IS NULL
    When a.c2 is not a foreign key, the condition in the second query becomes
    Code:
    WHERE NOT EXISTS (SELECT 1 FROM tableb WHERE c2 = a.c2)
    - A FULL OUTER JOIN can always be emulated with a "UNION ALL" of three queries, one on the inner join, one on the first table (as above), and one on the second table.
    Last edited by Peter.Vanroose; 11-04-05 at 12:16.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  13. #13
    Join Date
    Jan 2003
    Location
    New York
    Posts
    160
    Whatever you guys decide which way is the best - i know that the way Madafaka first used works for me. So thanks
    Beyond Limitation

Posting Permissions

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