Another SQL search problem - 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)

Another SQL search problem

Author

Topic

mm512
Starting Member

3 Posts

Posted - 2005-03-05 : 13:10:55

ok three inputs
keyword = string
stateid = int
and date = int

looks something like this
SELECT * FROM cj_jobs INNER JOIN cj_employers ON cj_jobs.j_e_id = cj_employers.e_id WHERE 1=1 AND j_title LIKE '%Test%' OR j_description LIKE '%Test%' AND j_startdate = CONVERT(DATETIME,'2/26/2005 11:46:38 AM',102) AND j_enabled = 1 ORDER BY j_featuredjob DESC;

but too many results come back. Any help is greatly appreciated.

I want it to return first an foremost the enabled jobs then the ones from the startdate forward then the ones that match the keyword or stateid querries...

Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-05 : 13:28:07

Try adding parens around the ORs:


SELECT * 
FROM cj_jobs 
JOIN cj_employers 
	ON cj_jobs.j_e_id = cj_employers.e_id 
WHERE	1=1 
AND	(j_title LIKE '%Test%' OR j_description LIKE '%Test%')
AND	j_startdate = CONVERT(DATETIME,'2/26/2005 11:46:38 AM',102) 
AND	j_enabled = 1 
ORDER BY 
	j_featuredjob DESC

Be One with the Optimizer
TG

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-03-05 : 14:25:25

If you want data from the Start Date forward, your WHERE clause probably should have something like this:

j_startdate >= CONVERT(DATETIME,'2/26/2005 11:46:38 AM',102)

or maybe this to get from the beginning of the start date forward

j_startdate >=  convert(datetime,convert(varchar(30),
		convert(datetime,'2/26/2005 11:46:38 AM'),102))

Also, what is the point of 1=1 in the WHERE clause?

quote:
Originally posted by mm512

ok three inputs
keyword = string
stateid = int
and date = int

looks something like this
SELECT * FROM cj_jobs INNER JOIN cj_employers ON cj_jobs.j_e_id = cj_employers.e_id WHERE 1=1 AND j_title LIKE '%Test%' OR j_description LIKE '%Test%' AND j_startdate = CONVERT(DATETIME,'2/26/2005 11:46:38 AM',102) AND j_enabled = 1 ORDER BY j_featuredjob DESC;

but too many results come back. Any help is greatly appreciated.

I want it to return first an foremost the enabled jobs then the ones from the startdate forward then the ones that match the keyword or stateid querries...

Thanks

CODO ERGO SUM

mm512
Starting Member

3 Posts

Posted - 2005-03-05 : 14:36:07

Monster thanks... THis works great now!!!! the parans worked out fine. The Where 1=1 was some scrap code for testing... deleted it out now. What's with the YAK? YOU GUYS JUST SAVED SOME HAIR FALLING OUT FOR ME!!!! I love this board!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources