select where in - 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

SQL Server 2000 Forums

Transact-SQL (2000)

select where in

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2005-03-03 : 12:48:42

I'm trying to do a select where in statement -- what am I doing wrong?



SELECT fundprograms.*,fundprograms.id as fundprogramid, geographic.geoeng, geographic.geoheb, fundprograms.geographicid, fundprograms.deadline FROM fundprograms LEFT JOIN geographic ON fundprograms.geographicid = geographic.ID WHERE (((fundprograms.geographicid)=5 or (fundprograms.geographicid)=1)) and (((fundprograms.deadline) between '1/1/2005' And '3/31/2005' or (fundprograms.deadline) is null) OR ((fundprograms.deadline2) between '1/1/2005' And '3/31/2005' )) order by name
where fundprogramid in(SELECT id FROM fundprograms WHERE CONVERT(char(4), jcgnewsyear) + REPLICATE('0', 2 - LEN(jcgnewsmonth)) + CONVERT(char(2), jcgnewsmonth) BETWEEN 
LEFT(CONVERT(char(8), CONVERT(datetime, '1/1/2004'), 112), 6) AND LEFT(CONVERT(char(8), CONVERT(datetime, '4/12/2005'), 112), 6))

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-03 : 12:58:48

you have a Where followed by an Order by followed by Where again.
consider using exists or a left join instead of IN operator


SELECT 	fundprograms.*,	fundprograms.id as fundprogramid, geographic.geoeng, geographic.geoheb, fundprograms.geographicid, fundprograms.deadline 
FROM 	fundprograms 
	LEFT JOIN geographic ON fundprograms.geographicid = geographic.ID 
WHERE 	(((fundprograms.geographicid)=5 or (fundprograms.geographicid)=1)) and (((fundprograms.deadline) between '1/1/2005' And '3/31/2005' or (fundprograms.deadline) is null) 
	OR ((fundprograms.deadline2) between '1/1/2005' And '3/31/2005' )) 
	and fundprogramid in (SELECT id FROM fundprograms WHERE CONVERT(char(4), jcgnewsyear) + REPLICATE('0', 2 - LEN(jcgnewsmonth)) + CONVERT(char(2), jcgnewsmonth) BETWEEN 
	LEFT(CONVERT(char(8), CONVERT(datetime, '1/1/2004'), 112), 6) AND LEFT(CONVERT(char(8), CONVERT(datetime, '4/12/2005'), 112), 6))
order by name

Go with the flow & have fun! Else fight the flow

Richard Branson
Yak Posting Veteran

84 Posts

Posted - 2005-03-03 : 13:02:03

How's this?


SELECT  F.*,
	F.id as fundprogramid, 
	G.geoeng,
	eographic.geoheb, 
	F.geographicid, 
	F.deadline 
FROM fundprograms F
LEFT JOIN geographic G
ON F.geographicid = G.ID 
WHERE (F.geographicid= 5 or F.geographicid = 1)
and (
		(F.deadline between '1/1/2005' And '3/31/2005') 
		or (F.deadline is null) 
		OR (F.deadline2 between '1/1/2005' And '3/31/2005')
    ) 
and fundprogramid in
	(
	SELECT id 
	FROM fundprograms 
	WHERE CONVERT(char(4), jcgnewsyear) + REPLICATE('0', 2 - LEN(jcgnewsmonth)) + CONVERT(char(2), jcgnewsmonth) BETWEEN 
		LEFT(CONVERT(char(8), CONVERT(datetime, '1/1/2004'), 112), 6) 
	AND LEFT(CONVERT(char(8), CONVERT(datetime, '4/12/2005'), 112), 6))
order by name

You can't teach an old mouse new clicks.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-03-03 : 13:02:36

EDIT:

it would help you a ton to actually do some formating with your code.

this is what you have:


SELECT 
	fundprograms.*,
	fundprograms.id as fundprogramid, 
	geographic.geoeng, 
	geographic.geoheb, 
	fundprograms.geographicid, 
	fundprograms.deadline 
FROM fundprograms 
LEFT JOIN geographic 
ON fundprograms.geographicid = geographic.ID 
WHERE 
(
	(fundprograms.geographicid=5 or fundprograms.geographicid=1)
)
and 
(
	(fundprograms.deadline between '1/1/2005' And '3/31/2005' or fundprograms.deadline is null) 
	OR 
	(fundprograms.deadline2 between '1/1/2005' And '3/31/2005' )
) 
order by name

where fundprogramid in(SELECT id FROM fundprograms WHERE CONVERT(char(4), jcgnewsyear) + REPLICATE('0', 2 - LEN(jcgnewsmonth)) + CONVERT(char(2), jcgnewsmonth) BETWEEN 
LEFT(CONVERT(char(8), CONVERT(datetime, '1/1/2004'), 112), 6) AND LEFT(CONVERT(char(8), CONVERT(datetime, '4/12/2005'), 112), 6))

Whats the 2nd where clause for?? That might be a good place to start debugging

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2005-03-03 : 13:08:06

thanks i just noticed that.
It's confusing as I am generating the sql statement dynamically with code -- I then want to filter it out with the last statement --to filter based on the date and the newslettermonght and year. --- any suggestions on the best way to go about this. -- it is basically a select statement within a select but because I am generating it automatically it sometimes has a where at the end and sometimes does not.

Any idea?

Richard Branson
Yak Posting Veteran

84 Posts

Posted - 2005-03-03 : 13:12:23

Put a where in your "Dynamic Query Producer" that will stop the query from inserting the Second where clause when the conditions have been met not to put it in

You can't teach an old mouse new clicks.

htaquee
Starting Member

1 Post

Posted - 2005-09-21 : 18:47:09

I will appreciate if anyone can help me in this query.

I am trying to find a range of date from a StrDate field. But it also brings beside the range specified.
******************************************
Select * from tblX
Where date1 between '8/25/2005' and '9/10/2005'
********************************************
When i run this query, i also get records of '8/1/2005' , 2,3,4,...etc
Please remember, here "date1" is a VarChar2 (String)

I tried same query in MySQL and MS-Access.
Where am I going wrong?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-21 : 19:54:44

first thig you're doing wrong is storing this as a varchar field.
store it as a datetime.
that way sql server doesn't know this is a date so it does an ascii comparison.
use convert:
Where convert(datetime, date1) between '8/25/2005' and '9/10/2005'

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources