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
 LEFT JOIN query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SqlGirl87
Starting Member

26 Posts

Posted - 11/22/2012 :  07:46:57  Show Profile  Reply with Quote
hoping this is a simple one,

I'm trying to run the below


SELECT M.Salesperson, M.Tsales, A.Tsales FROM Salesmast M (nolock)
	LEFT OUTER JOIN Salesaud A 
	ON M.Salesperson = A.Salesperson AND M.Tsales <> A.Tsales


I need to to be a left join so it will return the salesperson even if they are not in the audit table, But, what I want is, IF they are in the audit table and mast table then M.Tsales <> A.Tsales


Currently, it is returning records where the salesperson matches the audit table, but the Tsales match to, so instead of returning no line for that it returns

M.Salesperson M.Tsales A.Tsales
PERSON VALUE NULL

Please note that this is the tail end of a much bigger join query, any advice on the simplest way this can be achieved?

Thanks

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 11/22/2012 :  08:37:06  Show Profile  Reply with Quote

SELECT M.Salesperson, M.Tsales, A.Tsales FROM Salesmast M (nolock)
	LEFT OUTER JOIN Salesaud A 
	ON M.Salesperson = A.Salesperson 
WHERE A.Salesperson IS NULL
OR    M.Tsales <> A.Tsales



KH
Time is always against us

Go to Top of Page

SqlGirl87
Starting Member

26 Posts

Posted - 11/22/2012 :  10:11:06  Show Profile  Reply with Quote
quote:
Originally posted by khtan


SELECT M.Salesperson, M.Tsales, A.Tsales FROM Salesmast M (nolock)
	LEFT OUTER JOIN Salesaud A 
	ON M.Salesperson = A.Salesperson 
WHERE A.Salesperson IS NULL
OR    M.Tsales <> A.Tsales



KH
Time is always against us






Thank you for the response, but that hasn't worked, it returns the same as my query. Please see below:



PRINT 'Salesmast'
SELECT * FROM salesmast

PRINT ''
PRINT 'Salesaud'
SELECT * FROM salesaud

SELECT M.Salesperson, M.Tsales, A.Tsales FROM Salesmast M (nolock)
	LEFT OUTER JOIN Salesaud A 
	ON M.Salesperson = A.Salesperson AND M.Tsales <> A.Tsales

SELECT M.Salesperson, M.Tsales, A.Tsales FROM Salesmast M (nolock)
	LEFT OUTER JOIN Salesaud A 
	ON M.Salesperson = A.Salesperson 
WHERE A.Salesperson IS NULL
OR    M.Tsales <> A.Tsales


Salesmast
Salesperson TSales
----------- ---------------------
1           50.00
2           100.00

(2 row(s) affected)

 
Salesaud
Salesperson TSales
----------- ---------------------
2           150.00

(1 row(s) affected)

Salesperson Tsales                Tsales
----------- --------------------- ---------------------
1           50.00                 NULL
2           100.00                150.00

(2 row(s) affected)

Salesperson Tsales                Tsales
----------- --------------------- ---------------------
1           50.00                 NULL
2           100.00                150.00

(2 row(s) affected)


What I am trying to do is only return rows where the Tsales value don't match IF the salesperson exists in both tables by a join. So in this example, salesperson 1 would be excluded as there is no record of him in the audit table. I know I could use a WHERE NOT IN but that won't work because of other joins...

Thanks


Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/22/2012 :  10:27:44  Show Profile  Reply with Quote
quote:
I need to to be a left join so it will return the salesperson even if they are not in the audit table


quote:
So in this example, salesperson 1 would be excluded as there is no record of him in the audit table.

It looks like the requirement in the latest post is different from what you seemed to suggest in the original post. Based on your latest post, you don't need a left join. You can change the join to INNER JOIN and keep everythign else exactly as you have it now.
Go to Top of Page

SqlGirl87
Starting Member

26 Posts

Posted - 11/22/2012 :  11:13:43  Show Profile  Reply with Quote
quote:
Originally posted by sunitabeck

quote:
I need to to be a left join so it will return the salesperson even if they are not in the audit table


quote:
So in this example, salesperson 1 would be excluded as there is no record of him in the audit table.

It looks like the requirement in the latest post is different from what you seemed to suggest in the original post. Based on your latest post, you don't need a left join. You can change the join to INNER JOIN and keep everythign else exactly as you have it now.




I thought perhaps I didn't explain it right so an example would make it clear.

Thnaks for the advice, I think the Inner join when I first tried it affected the results of another table I needed to join on but I'll give it another try
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/25/2012 :  09:38:27  Show Profile  Reply with Quote
quote:
Originally posted by SqlGirl87

quote:
Originally posted by sunitabeck

quote:
I need to to be a left join so it will return the salesperson even if they are not in the audit table


quote:
So in this example, salesperson 1 would be excluded as there is no record of him in the audit table.

It looks like the requirement in the latest post is different from what you seemed to suggest in the original post. Based on your latest post, you don't need a left join. You can change the join to INNER JOIN and keep everythign else exactly as you have it now.




I thought perhaps I didn't explain it right so an example would make it clear.

Thnaks for the advice, I think the Inner join when I first tried it affected the results of another table I needed to join on but I'll give it another try



then you must give us full picture for us to suggest you with accurate solution for your scenario

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

SqlGirl87
Starting Member

26 Posts

Posted - 11/25/2012 :  15:53:09  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

quote:
Originally posted by SqlGirl87

quote:
Originally posted by sunitabeck

quote:
I need to to be a left join so it will return the salesperson even if they are not in the audit table


quote:
So in this example, salesperson 1 would be excluded as there is no record of him in the audit table.

It looks like the requirement in the latest post is different from what you seemed to suggest in the original post. Based on your latest post, you don't need a left join. You can change the join to INNER JOIN and keep everythign else exactly as you have it now.




I thought perhaps I didn't explain it right so an example would make it clear.

Thnaks for the advice, I think the Inner join when I first tried it affected the results of another table I needed to join on but I'll give it another try



then you must give us full picture for us to suggest you with accurate solution for your scenario

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/





I just needed
LEFT OUTER JOIN

...

ON (M.Salesperson = A.Salesperson AND M.Tsales <> A.Tsales)

instead of

ON M.Salesperson = A.Salesperson AND M.Tsales <> A.Tsales
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 11/25/2012 :  21:41:59  Show Profile  Reply with Quote
quote:
I just needed

LEFT OUTER JOIN

...

ON (M.Salesperson = A.Salesperson AND M.Tsales <> A.Tsales)

instead of

ON M.Salesperson = A.Salesperson AND M.Tsales <> A.Tsales



there is not logic different between that two. The parenthesis is optional.


KH
Time is always against us

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.16 seconds. Powered By: Snitz Forums 2000