Database Discussion - SQL problem

Print Page | Close Window

SQL problem

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=14859
Printed Date: 29 March 2026 at 6:17pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: SQL problem

Posted By: ainsworth14
Subject: SQL problem
Date Posted: 27 April 2005 at 4:22pm

Hi all, i am trying to query my access table from my website with the following SQL:

record_sql = "SELECT * FROM record_overview WHERE ARTIST LIKE '%" & searchword & "%' OR TITLE LIKE '%" & searchword & "%' OR DESCRIPTION LIKE '%" & searchword & "%' AND STOCK > 0 ORDER BY ARTIST ASC"

the problem is that it all works fine except for the AND Stock >0 bit - it still brings back items that have 0 in the field.

any ideas?

thanks

mark

Replies:

Posted By: michael
Date Posted: 27 April 2005 at 4:54pm

It's like High-School Math. Put brackets around it

record_sql = "SELECT * FROM record_overview WHERE (ARTIST LIKE '%" & searchword & "%' OR TITLE LIKE '%" & searchword & "%' OR DESCRIPTION LIKE '%" & searchword & "%') AND STOCK > 0 ORDER BY ARTIST ASC"

-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker

Posted By: ainsworth14
Date Posted: 27 April 2005 at 5:09pm

yup worked a treat

thanks

ps i'm fairly new to working with sql statements!

Posted By: Lofty
Date Posted: 28 April 2005 at 3:19am

aye. bit more of an explanation. using Or in the sql statement is like resetting Where statement, it whipes all the previous where & And stuff each Or is starting fresh.

so the stock > 0 is only applicable to doing a search for DESCRIPTION

if you went
ARTIST LIKE '%" & searchword & "%' AND STOCK > 0 OR TITLE LIKE '%" & searchword & "%' AND STOCK > 0 OR DESCRIPTION LIKE '%" & searchword & "%' AND STOCK > 0

that would also work.. but obviously the brackets is a much better solution. I just thought ya might want to know why.

Posted By: ainsworth14
Date Posted: 28 April 2005 at 2:21pm

thanks a lot for the explanation its a great help

thanks

mark