JOIN to get ALL results, even nonmatching

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

SQL Server 2008 Forums

Transact-SQL (2008)

JOIN to get ALL results, even nonmatching

Author

Topic

mattboy_slim
Yak Posting Veteran

72 Posts

Posted - 2014-01-02 : 16:49:42

Sorry if the subject line is a poor description. I have two tables to join, but I want to get ALL the results from one table (tb_updatesdetected), including the matches, even if the match is with something I'm not selecting. It's difficult to explain, so here is a quick sample of data with the two tables:


tb_updatesdetected:                tb_updatesapproved:
f_updatesdetectedname              f_updateprofile	f_updatename
UpdateA                            Profile1	      UpdateA
UpdateB                            Profile2	      UpdateA
UpdateC                            Profile1	      UpdateB
UpdateD                            Profile2	      UpdateC

Current, minimal query:


SELECT f_updatename, f_updateprofile, f_updatesdetectedname
FROM tb_updatesdetected
LEFT JOIN tb_updatesapproved
ON f_updatesdetectedname = f_updatename
WHERE (f_updateprofile = 'Profile1' or f_updateprofile IS NULL)
ORDER BY f_updatename DESC

Current Result:


f_updatename     f_updateprofile	f_updatesdetectedname
UpdateB	     Profile1	      UpdateB
UpdateA	     Profile1	      UpdateA
NULL		     NULL	            UpdateD

Desired Result:


f_updatename     f_updateprofile	f_updatesdetectedname
UpdateB          Profile1	      UpdateB
UpdateA          Profile1	      UpdateA
??	           ??		      UpdateC
NULL	           NULL		      UpdateD

Notice how "UpdateC" is in the "Desired Result", but is not in the "Current Result". I see "UpdateD", which I do indeed want, because it is not assigned to any profile at all.

Effectively, I want to get ALL results from "tb_updatesdetected", so I know which updates are applied to Profile1 and are also not applied to Profile1. Whether they are applied only to Profile2 is irrelevant.

EDIT #1: I should state that the number/names of the profiles is unknown, so the query cannot say "WHERE f_updateprofile <> 'Profile2'.
EDIT #2: An update can be a member of multiple profiles. Note that "UpdateA" is assigned to 'Profile1' and 'Profile2'. I'd like to see that "UpdateC" is available to assign to 'Profile1', since it isn't already assigned.

I've tried every form of JOIN, GROUPing, and DISTINCT to no avail, so I'm here to beg for help.

Thanks in advance, sincerely,
Matt

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-01-02 : 17:44:40

Not 100% sure I understand specifically what you need, but maybe this:



SELECT f_updatename, f_updateprofile, f_updatesdetectedname
FROM tb_updatesdetected
LEFT JOIN tb_updatesapproved
    ON f_updatesdetectedname = f_updatename 
    AND f_updateprofile = 'Profile1' 
ORDER BY f_updatename DESC

mattboy_slim
Yak Posting Veteran

72 Posts

Posted - 2014-01-02 : 18:06:34

Thank you! I'm not sure I understand why it works with the addition of the "AND f_updateprofile = 'Profile1'" line though.

I can't find documentation on the "AND" operator with a JOIN, probably because "and" is discarded in search results or treated as Boolean.

Do you have a link you could point to that would explain this?

Again, thank you, I hit my head on my desk for literally hours because of this.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-01-02 : 19:47:13

When joining tables, the ON portion is just a boolean expression that gets evaluated (in theory) for every combination of rows in the two tables. It needs to evaluate to true in order for the two rows to match. So using an AND or an OR is just part of defining that boolean expression. (Don't tell anyone but the boolean expression does not have to have anything to do with the tables at all; it's just a boolean expression. If it's true the rows match; if it's not true, they won't.)

=================================================
A man is not old until regrets take the place of dreams. - John Barrymore

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-03 : 01:34:42

quote:
Originally posted by mattboy_slim

Thank you! I'm not sure I understand why it works with the addition of the "AND f_updateprofile = 'Profile1'" line though.

I can't find documentation on the "AND" operator with a JOIN, probably because "and" is discarded in search results or treated as Boolean.

Do you have a link you could point to that would explain this?

Again, thank you, I hit my head on my desk for literally hours because of this.

see
http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/criteria-on-outer-joined-tables.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources