MySQL Forums
Forum List  »  General

Multi-Table Query Problems
Posted by: nick
Date: March 14, 2005 08:58AM

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%')
GROUP BY v.vbusiness

Where am I going wrong? The results are just wrong.

TIA,

Nick

Options: ReplyQuote


Subject
Written By
Posted
Multi-Table Query Problems
March 14, 2005 08:58AM
March 14, 2005 09:02AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.