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.
| Author |
Topic |
|
tech_1
Posting Yak Master
129 Posts |
 Posted - 2014-12-15 : 12:57:01
|
Hello.I have a view which joins 2 tables together in a left outer join.Now, the left outer join table (lets call this SalesData) may or may not have records. As months and years go by, records will be inserted or updated.I am trying to find a query which will do the following:Bring back all records where the branch matches an input value and also:- bring back NULL records for the YR and MO column- bring back values which match the input params for YR and MO column.this works as expected:quote:
SELECT wmtecp.*FROM v_stores wmtecp RIGHT OUTER JOIN v_Stores roj ON wmtecp.ID = roj.ID WHERE wmtecp.TM = @p1 AND 1=1 AND roj.YR IS NULL AND roj.MO IS NULL OR wmtecp.MO = 11 AND wmtecp.YR = 2014
this is right outer joining the view to itself and prefer it this way.Here is the thing: I want to bring back only distinct records (distinct by store ID I guess).We are bringing back result set which contains NULL and NON null values but for both result sets the same storeID is returned, I do not want this but to only bring back the same record once either WITH values or WITHOUT values.thoughts on how to do this? |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2014-12-15 : 19:13:35
|
I don't see why you are joining the View to itself but perhaps I don't have the insight someone closer to the data would have...SELECT wmtecp.*FROM v_stores wmtecp WHERE wmtecp.TM = @p1AND ( (wmtecp.YR IS NULL AND wmtecp.MO IS NULL) OR (wmtecp.MO = 11 AND wmtecp.YR = 2014) ) ???
No amount of belief makes something a fact. -James Randi |
 |
|
|
|
|
|
Right outer join and distinct