Please help with query

[lingo5]

hi,

I have a table called "products" where i store product info including id_family.

I have another table called "families" where I store the family_name and id_family.

 

This is how I get all my products from the DB:

 

mysql_select_db($database_MySQLconnect, $MySQLconnect);
$query_products_RS = "SELECT * FROM t_products ORDER BY product_titulo_esp ASC";
$query_limit_products_RS = sprintf("%s LIMIT %d, %d", $query_products_RS, $startRow_products_RS, $maxRows_products_RS);
$productsRS = mysql_query($query_limit_productsRS, $MySQLconnect) or die(mysql_error());
$row_products_RS = mysql_fetch_assoc($products_RS);

 

...this works fine.

 

Whay i need to do now is to get the family_name from the table "families" for each product based on the id_family column from the "products" table.....

 

do I make sense?....thanks!!

[Muddy_Funster]

You need to use a JOIN to link the tables using the relational column. Something like this :

 $query = <<<SQL
SELECT field, name, list
FROM t_products
LEFT JOIN t_families
 ON(t_products.id_family = t_families.id_family)
ORDER BY product_titulio_esp

SQL;

[NomadicJosh]

What you need to do is called a join. Example:

 

$q = "SELECT 
product_id,
product_title,
family_name,
prod.id_family,
fam.id_family 
FROM products 
LEFT JOIN family 
ON prod.family_id = prod.family_id";

 

I am using an alias with id_family because I am assuming that the id names are the same in both tables.

[Muddy_Funster]

What you need to do is called a join. Example:

 

$q = "SELECT
product_id,
product_title,
family_name,
prod.id_family,
fam.id_family
FROM products
LEFT JOIN family
ON prod.family_id = prod.family_id";

 

I am using an alias with id_family because I am assuming that the id names are the same in both tables.

did you even read that before you posted it?

 

$q = "SELECT

product_id,

product_title,

family_name,

prod.id_family, <<--- prod is not a table

fam.id_family <<---fam is also not a table. Why would you want the same information twice anyway?

FROM products <<-----no alias applied to the table name

LEFT JOIN family <<-----as above

ON prod.family_id = prod.family_id"; <<----your linking the same field in the same table to the same field in the same table!!

[lingo5]

Thank you both !!!...it works now !!!! .... :happy-04: :happy-04:

 

Muddy_Funster I love you

Edited January 4, 2013 by lingo5