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

    Join Date
    Nov 2012
    Posts
    1
    Rep Power
    0

    Data comparison against more than 2 tables?


    Good morning (evening?)

    I'm racking my brain, can't come up with the right answer.

    New to the forum, professional novice with sql...

    I apologize if this is not formatted or asked correctly. I'm mostly self-taught, so my terminology (and syntax) may be very wrong...

    I'm working on a db, for the sake of this question, there are 4 tables I need to work with.

    I need to determine how to write a where clause that compares a value amongst three of these tables. They are joined on different values, but all contain the same value that I'm trying to compare...may be easier to show you what I've got and explain what I am trying to do...?

    select
    distinct(a.order_invoice_number),
    b.order_status_desc
    from
    dbo.e_script_msg_attributes a,
    dbo.order_status b,
    dbo.order_header c,
    dbo.workflow_transactions d
    where
    a.order_invoice_number = c.order_invoice_number and
    a.e_script_msg_attribute_seq = d.e_script_msg_attribute_seq and
    c.order_num = d.order_num and
    c.order_status_num = b.order_status_num and
    a.trading_partner_num != d.trading_partner_num and
    b.order_status_num in (1,2)

    This gets me results that are valid, however I'm not sure if it's all of them.

    My problem statement is: There are instances in my application where the trading_partner_num values on a, c, d get out of sync. I'm trying to write a query that will return all the order_invoice_numbers where the trading_partner_num values do not match in any of the three tables.

    I believe my joins are correct, if not ideally written, but I do not know how to write the comparison of the trading_partner_num values among the three tables and identify any order_invoice_numbers where there is a discrepancy.

    I appreciate your help in advance, and apologize for the first-post-hail mary "help me"...

    Dan
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    first of all, please don't do this --
    Code:
    select distinct(somecolumn), ...
    DISTINCT is not a function, and putting the first column that comes after it into parentheses has no effect on the way it actually works

    for your comparisons, try this --
    Code:
    SELECT DISTINCT
           sma.order_invoice_number
         , os.order_status_desc
      FROM dbo.order_header AS oh
    INNER
      JOIN dbo.order_status AS os
        ON os.order_status_num = oh.order_status_num
       AND os.order_status_num in (1,2)
    INNER
      JOIN dbo.e_script_msg_attributes AS sma
        ON sma.order_invoice_number = oh.order_invoice_number 
    INNER
      JOIN dbo.workflow_transactions AS wt
        ON wt.e_script_msg_attribute_seq = sma.e_script_msg_attribute_seq
       AND wt.order_num = oh.order_num 
     WHERE sma.trading_partner_num <> oh.trading_partner_num 
        OR sma.trading_partner_num <> wt.trading_partner_num 
        OR oh.trading_partner_num <> wt.trading_partner_num
    r937.com | rudy.ca
    please visit Simply SQL and buy my book

IMN logo majestic logo threadwatch logo seochat tools logo