SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Data comparison against more than two tables...
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tonkatruckjk
Starting Member

USA
2 Posts

Posted - 11/08/2012 :  02:07:44  Show Profile  Reply with Quote
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

Edited by - tonkatruckjk on 11/08/2012 02:15:00

webfred
Flowing Fount of Yak Knowledge

Germany
8768 Posts

Posted - 11/08/2012 :  02:49:43  Show Profile  Visit webfred's Homepage  Reply with Quote
Maybe this?

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
b.order_status_num in (1,2) and
(
a.trading_partner_num != c.trading_partner_num OR
a.trading_partner_num != d.trading_partner_num OR
c.trading_partner_num != d.trading_partner_num
)


Too old to Rock'n'Roll too young to die.
Go to Top of Page

tonkatruckjk
Starting Member

USA
2 Posts

Posted - 11/08/2012 :  03:03:33  Show Profile  Reply with Quote
webfred - thanks! looks like that's giving me more results (that's what I was expecting)

I had tried this in-line with the or statements (without the parenthesis) and the query was taking forever to run - I'm not exactly sure what the difference is, but I like it.

Thanks!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000