[SOLVED] Search query question
Posted: Mon Jan 07, 2008 5:45 am
pickle | Please use
As you can see there are 2 search attributes from the attributes table being searched. It obviously doesn't work as I found out after a bit of a braindead moment. It pulls results where one a.name OR the 2nd a.name are true.
What i want to do it pull out where only both are true.
So it would only pull results where
AND
Are both true.
Any ideas?
pickle | Please use[/syntax]
Code: Select all
,Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
I have a query searching multiple tables based on search input. The largest of the tables searched is one by Openrange, a 3rd party product info supplier. The way the information they send is laid out, is causing me problems when searching more than one attribute. Due to the nature of the way this info is used in other parts of the site it's not really possible to layout the info in the database in a different manner.
The query is :
[syntax="sql"]
SELECT SQL_CALC_FOUND_ROWS p.productID,p.name,p.shortdescription,p.price1,p.scLevel,p.thumbnail,p.code FROM jss_products AS p,jss_products_tree AS t LEFT JOIN attributes AS a ON p.extrafield2=a.productid WHERE p.productID=t.productID AND t.sectionID='3909' AND p.visible='Y' AND p.price1>0 AND ((a.name='Interface Type(s)' AND a.value='PATA') OR (a.name='Disk Capacity' AND a.value<=500 AND a.value>=250) ) GROUP BY p.productID ORDER BY p.price1 ASC LIMIT 0,20
What i want to do it pull out where only both are true.
So it would only pull results where
Code: Select all
a.name='Interface Type(s)' AND a.value='PATA'
Code: Select all
a.name='Disk Capacity' AND a.value<=500 AND a.value>=250
Any ideas?
pickle | Please use[/syntax]
Code: Select all
,Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]