"Nick Zukin" <[EMAIL PROTECTED]> wrote on 03/14/2005 05:22:38 PM:
> I'm trying to do a multitable query and am having problems. > > I have three tables: vendors, products, and vendorproducts. The > vendorproducts table creates a many to many relationship between the vendors > and the products. There is nothing more than the vendor and product ids in > the vendorproducts table. > > I want to be able to create a query that will find vendors who have certain > products. However, I'm trying to make a keyword search (PHP/MySQL) so that > using form data I can search multiple columns for the same keyword. Here's > how I am currently doing the query: > > $query = "SELECT v.vbusiness, v.vcategory, v.vurl, v.vcity, v.vstate, > v.vendorid "; > $query .= "FROM vendorproducts AS vp "; > $query .= "INNER JOIN vendors AS v ON vp.vpvendorid = v.vendorid "; > $query .= "INNER JOIN products AS p ON vp.vpvendorid = p.productid "; > $query .= "WHERE (p.productname LIKE '%".$_GET['keyword']."%') "; > $query .= "OR (p.productfamily LIKE '%".$_GET['keyword']."%') "; > $query .= "OR (v.vcategory LIKE '%".$_GET['keyword']."%') "; > $query .= "GROUP BY v.vbusiness "; > > As an example, it might look like this: > > SELECT v.vbusiness, v.vcategory, v.vurl, v.vcity, v.vstate, v.vendorid > FROM vendorproducts AS vp > INNER JOIN vendors AS v ON vp.vpvendorid = v.vendorid > INNER JOIN products AS p ON vp.vpvendorid = p.productid > WHERE (p.productname LIKE '%Apples%') > OR (p.productfamily LIKE '%Apples%') > OR (v.vcategory LIKE '%Apples%') > GROUP BY v.vbusiness > > Where am I going wrong? The results aren't random, but I can't see how > they're coming up with what they're coming up with. > > TIA, > > Nick > > > You are "GROUPING" when you shouldn't. You only need to GROUP whenever you want to perform some kind of aggregate function (AVG, SUM, COUNT, etc). What you see is a pseudo-random result from all of the possible results that meet your criteria. Drop your GROUP BY clause and your results should come back into line. If you were trying to eliminate duplicate rows, you might want to try SELECT DISTINCT. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
