A complex query

At least to me.

[GGR_Web]

I have a table that I want to display the contents of.
The order needs to be by productgroup and these productgroups need to be grouped together under their respective parent groups, which are stored in a seperate table. So far I have:

$query = "SELECT * FROM product WHERE product.models LIKE '%,411' OR product.models LIKE '411,%' OR product.models LIKE '%,411,%' OR product.models LIKE '411' ORDER BY product.productgroup";

For some reason I'm not seeing, when I add the second table I need to order by I get strange results.

[edited by: GGR_Web at 3:08 pm (utc) on Feb. 10, 2009]

[d40sithui]

Not clear on your table schema, but from what I can tell::

$query = "SELECT t1.*, t2.* FROM product t1, productParentGroup t2 WHERE t1.models LIKE '%,411' OR t1.models LIKE '411,%' OR t1.models LIKE '%,411,%' OR t1.models LIKE '%411%' AND t1.productgroup=t2.productgroup ORDER BY t1.productgroup";

[GGR_Web]

t2.productgroup

should this be identical to t1.productgroup?

(in the actual table I mean)

[d40sithui]

Yeah, I am assuming "productgroup" is the foreign key from your product table and the primary key for your parent table.
Would be helpful if you show your table schema.

[GGR_Web]

OK

---

t1.(product)
partno name model productgroup description price brand

suggestions groups pic saleonline general generaltext

subarumodels

---

t2(productgroup)
id name parent notes pic parent2 suggestions subaru

---

The productgroup from t1 is a number that relates to the id in t2. I need to group those id numbers under their respective parents (these are numbers that relate to the table id - the parent information is not stored on a separate table)

Apologies for the time taken to reply.
I'm not really sure what schema is relevant - I'm still learning this. Just ask if you need anything else.

[edited by: GGR_Web at 1:40 pm (utc) on Feb. 16, 2009]