LEFT JOIN query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

LEFT JOIN query

Author

Topic

SqlGirl87
Starting Member

26 Posts

Posted - 2012-11-22 : 07:46:57

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)

17689 Posts

Posted - 2012-11-22 : 08:37:06

[code]
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
[/code]

KH
[spoiler]Time is always against us[/spoiler]

SqlGirl87
Starting Member

26 Posts

Posted - 2012-11-22 : 10:11:06

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
[spoiler]Time is always against us[/spoiler]

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-22 : 10:27:44

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.

SqlGirl87
Starting Member

26 Posts

Posted - 2012-11-22 : 11:13:43

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-25 : 09:38:27

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/

SqlGirl87
Starting Member

26 Posts

Posted - 2012-11-25 : 15:53:09

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-11-25 : 21:41:59

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
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources