Exluding rows - 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

Exluding rows

Author

Topic

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2008-03-12 : 02:39:30

Hi All,

I have a query that seems pretty basic, but it's not returning the expected results. Please consider the following query:

Select b.fundingsourceid1, cfs.fundingsourceid

From Booking B
JOIN ClientFundingSource CFS
ON b.clientid=cfs.clientid

Where b.ldate=?
AND b.fundingsourceid1 not in (select cfs.fundingsourceid from clientfundingsource cfs)

I'm returning the following results:

FundingSourceID1 FundingSourceID
1 1
1 2
1 3

when I am trying to only return the following results:

FundingSourceID1 FundingSourceID
1 2
1 3

As usual...thanks much!

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2008-03-12 : 02:47:22

Use unique alias

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-12 : 03:44:15

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

E 12°55'05.25"
N 56°04'39.16"

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2008-03-12 : 04:25:41

Thank you Koji, I'll try that...Peso, not really sure how to phrase the question the way you're asking. I'm obviously not as technically knowledgable as I should be in order to post on this site. If need be, I can move to another site. My SQL interface is extremely limited. We run SQL server 2005, but the software we use is proprietary and we're precluded from doing anything but Select queries and it must be in a certain format. I'm learning SQL from reading do it yourself books which is challenging as the standard syntax that is taught in the books I'm reading does not work with the interface. I appreciate the help that I've gotten here, I honestly don't know how better to phrase the questions. Feel free not to respond. Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-03-12 : 04:39:49

try see if this is what you want ?

SELECT 	b.fundingsourceid1, cfs.fundingsourceid
FROM 	Booking b
	LEFT JOIN ClientFundingSource cfs	
	ON 	b.clientid 		= cfs.clientid
	AND	fundingsourceid1 	= cfs.fundingsourceid 
WHERE 	cfs.fundingsourceid IS NULL

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

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2008-03-12 : 05:02:25

KHTAN...Thank you so much! That worked perfectly with the addition of:
and b.fundingsourceid1<>0

Thanks again!
Craig

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources