Combining queries?

[xiao]

I have two tables:

products and products_description, with products_id in common.

I need:

products_id, products_price and products_model from products

and products_name from products_description

where products_model is like '%ABC%'

 

Can I do that in one query?

Or do I have to get products_id, products_price and products_model from products

and do another query for each result to get the matching products_name?

 

How do I do that in one query?

 

 

[rhodesa]

As long as there is a one-to-one relationship between the tables, you can do:

 

SELECT p.products_id,p.products_price,p.products_model,pd.products_name FROM products p,products_description pd WHERE p.products_id = pd.products_id AND p.products_model LIKE '%ABC%'

[xiao]

So if I understand it correct, this can be right?

$result = mysql_query("SELECT products.products_id, products.products_model, products.products_price, 
products_description.products_name FROM products, products_description WHERE products.products_id = 
products_description.products_id AND products.products_model LIKE 'CEA%' OR products.products_model LIKE 'CEB%' OR 
products.products_model LIKE 'CDA%' OR products.products_model LIKE 'CDB%' OR products.products_model LIKE 'CJB%' OR 
products.products_model LIKE 'CGB%'") or die(mysql_error());

 

And the way you do it (much better imo ) it would be:

$result = mysql_query("SELECT pproducts_id, p.products_model, p.products_price, pd.products_name FROM products p, 
products_description pd WHERE p.products_id = pd.products_id AND p.products_model LIKE 'CEA%' OR p.products_model LIKE 'CEB%' OR 
p.products_model LIKE 'CDA%' OR p.products_model LIKE 'CDB%' OR p.products_model LIKE 'CJB%' OR p.products_model LIKE 'CGB%'") or 
die(mysql_error());

[rhodesa]

should work...to be safe, put parenthaseies around the group of ORs like so:

 

SELECT pproducts_id, p.products_model, p.products_price, pd.products_name FROM products p, 
products_description pd WHERE p.products_id = pd.products_id AND (p.products_model LIKE 'CEA%' OR p.products_model LIKE 'CEB%' OR 
p.products_model LIKE 'CDA%' OR p.products_model LIKE 'CDB%' OR p.products_model LIKE 'CJB%' OR p.products_model LIKE 'CGB%')