DESPERATE for help on SELECT
Posted: Fri Sep 09, 2005 8:22 pm
This should be simple but it does not appear to be
I have 2 tables in a mysql database members_products and category_wanted (plus the main "members" table)
They both contain similar data in tables (mem_products) and (product_name) respectively
They both have a common key being vendor_id (INT 11).
I need a single SELECT statement that searches both tables for a product ($product) and return the distinct(vendor_id) from BOTH tables where $product is found
current code is ;
<?
$sql="select distinct(c.vendor_id) from category_wanted c, members_products m
where ((c.product_name like '%$product%') or (m.mem_products like '%$product%'))
and c.vendor_id = m.vendor_id";
?>
This returns nothing because a vendor_id in category_wanted is not necessarily in members_products.
If I leave off the "and c.vendor_id = m.vendor_id" I get 4760 records returned no matter what I put in $product. (there are 13548 rows in category_wanted and 4 rows in members_products)"
I cannot use UNION as the host does not suppot it.
Any help would be greatly appreciated.
I have 2 tables in a mysql database members_products and category_wanted (plus the main "members" table)
They both contain similar data in tables (mem_products) and (product_name) respectively
They both have a common key being vendor_id (INT 11).
I need a single SELECT statement that searches both tables for a product ($product) and return the distinct(vendor_id) from BOTH tables where $product is found
current code is ;
<?
$sql="select distinct(c.vendor_id) from category_wanted c, members_products m
where ((c.product_name like '%$product%') or (m.mem_products like '%$product%'))
and c.vendor_id = m.vendor_id";
?>
This returns nothing because a vendor_id in category_wanted is not necessarily in members_products.
If I leave off the "and c.vendor_id = m.vendor_id" I get 4760 records returned no matter what I put in $product. (there are 13548 rows in category_wanted and 4 rows in members_products)"
I cannot use UNION as the host does not suppot it.
Any help would be greatly appreciated.