Smart(er) queries

[admin]

Hello

I would like to know what would be the best way to do query like this

I have table system like this:

products
gen_prodinfo
prod_group
groups

products and gen_prodinfo are connected - products has gen_prodinfo id as foreign key.
The reason for keeping two tables is that gen_prodinfo can contain info that would be similar to several products (picture, general description).
groups contain product groups and prod_group is table that allows to have n-n relations between groups and products.

Now I would like to do query that would first take results from gen_prodinfo and then display sp.ecific productinfo for it.

My current way of doing this is following:


SELECT DISTINCT gen.gen_id, gen.name, gen.pict
FROM gen_prodinfo gen LEFT JOIN products p ON (gen.gen_id=p.gen_id), prod_group pg
WHERE
p.prod_id=pg.prod_id AND pg.group_id='some_group_id'

That query gives me general product info. Then I use while loop like this and do second query inside it.

while($row = DBfetc_array(query_id)) {

SELECT prod_id, prod_name, prod_spec, prod_price FROM products WHERE gen_id='$row[gen_id]'

}

I hope you understand what I am trying to do

Thanks in advance,
Villu