subquery - mySQL, need help getting many sub-queries into 1 SELECT query (displays 13 columns) -

this first post. know horribly inefficient , repetitive code won't work, need combine these outputs 1 select statement. new @ this, i've been @ day , can't started in right direction, each snippet works on own...please help!

essentially i'm working db many tables, , right data each column, have account 3 tables joins.

thanks insight or help!

select    product.productid,   (   select abbreviation country   product   left join productcountry      on product.productid = productcountry.productid   left join location      on productcountry.locationid = location.locationid   group product.productid   ),    (   select r.resourcename manufacturer, rr.resourcename brand   product p   left join resource r      on p.manufacturecode = r.resourceid   inner join resource rr      on p.brandcode = rr.resourceid   ),    product.name,   product.upc,   product.size,    (   select unit.abbreviation measure   product   left join unit      on product.unit = unit.unitid   ),    (   select category.parentid category, category.description sub_category   product   left join productcategory      on product.productid = productcategory.productid   left join category      on productcategory.categoryid = category.categoryid   ),    (   select i.description ingredients, i.maycontain allergen_statement   product   left join ingredient      on product.productid = i.ingredientid   ),    (   select group_concat( special.description separator ', ' ) free_from   product   left join productspecial      on product.productid = productspecial.productid   left join special      on productspecial.specialid = special.specialid   group product.productid   )  product, productstatus productstatus.productstatusid = 1 

first, notes , assumptions...

• i'm assuming country column in location table, otherwise why bother joining it.

• if have trouble part, change second join left join. i've had occasional trouble doing left join b followed inner join b c. i've found it's easier keep left join going, example left-joins both tables:

    left join resource r      on p.manufacturecode = r.resourceid   inner join resource rr      on p.brandcode = rr.resourceid 

• you're joining resource in 2 different ways. that's ok mysql (and mainstream databases). have alias 1 or both of joins. i've aliased 1 of them:

  left join resource on product.manufacturecode ... left join resource brandresource on product.brandcode... 

• i don't know example how productstatus joined. you'll have supply that.

• start small using example below. join in country, when you've got working, join in manufacturer, brand, measure, etc. query isn't doing lot of advanced stuff; it's complicated due sheer number of tables. tackle them 1 @ time , you'll win :)

• finally, @bohemian noted in comments, group by can't brought top. actually, can, complicate things beyond belief. i've left subquery.

here's final result. note it's not tested because it's huge , don't have table structures or sample data. because it's huge :) @ rate, meant example only.

select    product.productid,   location.country,   resource.resourcename manufacturer,   brandresource.resourcename brand,   product.name,   product.upc,   product.size,   unit.abbreviation measure,   category.parentid category,   category.description sub_category,   ingredient.description ingredients,   ingredient.maycontain allergen_statement,   (select group_concat( special.description separator ', ' ) free_from     product     left join productspecial        on product.productid = productspecial.productid     left join special        on productspecial.specialid = special.specialid     group product.productid     ) product   inner join productstatus on ... it's joined   left join productcountry on product.productid = productcountry.productid   left join location on productcountry.locationid = location.locationid   left join resource on product.manufacturecode = resource.resourceid   left join resource brandresource on product.brandcode = brandresource.resourceid   left join unit on product.unit = unit.unitid   left join productcategory on product.productid = productcategory.productid   left join category on productcategory.categoryid = category.categoryid   left join ingredient on product.productid = i.ingredientid productstatus.productstatusid = 1