Page 1 of 1

DESPERATE for help on SELECT

Posted: Fri Sep 09, 2005 8:22 pm
by riparian
This should be simple but it does not appear to be :cry:

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.

Posted: Fri Sep 09, 2005 8:28 pm
by feyd
joins don't help for diparate tables. Use two seperate queries.

The tables are not disparated

Posted: Fri Sep 09, 2005 9:15 pm
by riparian
Both tables have a common key in (vendor_id).

If I was to rename both mem_products and category_wanted to say "mem_products" would there be a solution ?

I must have one $sql(select) as the program is an advanced search with 7 other sql select statements options.

Multiple selects would require 100s of lines of code.

Posted: Fri Sep 09, 2005 9:28 pm
by feyd
having a common field name means nothing when you're wanting to do two completely different selects in this situation. An extra select in this case shouldn't add much at all in terms of lines to your script.

Posted: Fri Sep 09, 2005 9:39 pm
by riparian
I was refering to the display and paging mechanisms in 5 search programs that have to be re-written if I have to do two selects.

With 2 selects I can easily put the results into one variable but when it comes the display
while($row=mysql_fetch_assoc) ..... the whole thing falls apart. The other searches in the program rely on this method.

Have you any suggestions ?

Brian

Posted: Fri Sep 09, 2005 9:49 pm
by feyd
refactoring. :)

edit: thinking about it, there's a potential where you could store the results from both, then use a function that has them and a static variable. Using the static to return "records" on each successive call.

Posted: Fri Sep 09, 2005 10:06 pm
by riparian
Thanks for the help anyway

Posted: Fri Sep 09, 2005 11:53 pm
by BruceT
Which MySQL version are you using? I think 5.x supports subselects. Failing that, you could create a temporary table with your first SELECT (say, from your 'c' table), then do a second SELECT from that temp table to match your other requirement (the 'm' table). It's a longer more involved process, but it allows you to work around the lack of subselects in MySQL versions that don't support them.

Posted: Fri Sep 09, 2005 11:55 pm
by feyd
considering he can't use union.. he's very likely running 3.x