JOIN with WHERE??

[sloth456]

I'm not too hopeful there is a solution for this but here goes

 

This is a simplified version of my tables

 

Table 1

 

id

product_name

1

jersey

2

trousers

 

Table 2

 

product_idattribute_codevalue

1price95

1brandmamut

2price105

2brandarcteryx

 

Is it possible to use a single SQL statement such that my result set looks something like this

 

idnamebrandprice

1jerseymammut95

2trousersarcteryx105

 

I thought about using JOIN, but I think with join you can only add seperate columns with different names.  In this example, there is only 1 column 'attribute_code' which needs to be joined on twice so that one column becomes 'price' and another 'brand'.

 

Something like

 

JOIN table2.attribute_code ON table1.id=table2.product_id WHERE table2.attribute_code = 'brand'
JOIN table2.attribute_code ON table1.id=table2.product_id WHERE table2.attribute_code = 'price'

 

But I don't think it would work.  I think I may have to achieve this with more than 1 query , but this is slow when I go through 4,000 records and each has 2 attributes.

 

Thanks to whoever has anything to say about this.

[AyKay47]

why not use an OR statement here instead of two queries?

judging from your tables, table one will be your left table, so you might have something like this.

 

select table_1.id, table_1.product_name, table_2.attribute_code, table_2.value left join table_2 on (table_1.id = table_2.product_id and (table_2.attribute_code = 'price' or table2.attribute_code = 'brand'))

 

your where clause would contain any filters that you want to place on the table_1 results.

[sloth456]

Wow, if that sql line really works, its a god send.  I'm back at work tommorow so I'll let you know how it goes.

[mikosiko]

this will do it:

 

 

SELECT table_1.product_name,
       GROUP_CONCAT(IF(table_2.attribute_code = 'brand',table_2.value,NULL)) AS 'Brand',
       GROUP_CONCAT(IF(table_2.attribute_code = 'price',table_2.value,NULL)) AS 'Price'
FROM table_1
           JOIN table_2 ON table_1.id = table_2.product_id
GROUP by table_1.id;

 

[sloth456]

Thanks for the replies guys.

 

So far I've tried the second solution which shows an error I've never seen before.

 

Here's my sql statement

 

SELECT catalog_product_entity.sku,cataloginventory_stock_item.qty,
	GROUP_CONCAT(IF(catalog_product_entity_decimal.attribute_id = '67',catalog_product_entity_decimal.value,NULL)) AS 'Price',
	GROUP_CONCAT(IF(catalog_product_entity_varchar.attribute_id = '146',catalog_product_entity_varchar.value,NULL)) AS 'Brand',
	GROUP_CONCAT(IF(catalog_product_entity_varchar.attribute_id = '134',catalog_product_entity_varchar.value,NULL)) AS 'Subtitle'
FROM catalog_product_entity
	JOIN cataloginventory_stock_item ON catalog_product_entity.entity_id = cataloginventory_stock_item.product_id
	JOIN catalog_product_entity_decimal ON catalog_product_entity.entity_id = catalog_product_entity_decimal.entity_id
                JOIN catalog_product_entity_varchar ON catalog_product_entity.entity_id = catalog_product_entity_varchar.entity_id
GROUP by catalog_product_entity.entity_id

 

Its a fair bit more complex than my example but its the same principle.  I actually have to gather data from 4 tables in total. 

 

- catalog_product_entity is my 'table1' it has an 'entity_id' column which relates to all the other tables.  It also stores an SKU column against these ids that I need.

 

- cataloginventory_stock_item contains the product stock level in a column named 'qty', it has an id column that matches the table1.entity_id (product_id)

 

- catalog_product_entity_decimal this table looks like table2 in my example, except the 'attribute_id' is a number not a code, 67 means price

 

- catalog_product_entity_varchar contains a brand and a subtitle, its just like my table2 example.  146 is brand and 134 is subtitle.

 

The error that I get is this

 

Incorrect key file for table '/tmp/#sql_3d9c_0.MYI'; try to repair it.

[sloth456]

Managed to figure it out with a bit of googling.  Seems I needed to increase the size limits on my tmp directory.

 

The query half works.  Here is the first row pulled off

 

Array (

[0] => 30019472

[sku] => 30019472

[1] => 1.0000

[qty] => 1.0000

[2] => 209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500

[Price] => 209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500,209.9500

[3] => Sram ,Sram ,Sram

[brand] => Sram ,Sram ,Sram

[4] => Black, 10-Speed, Medium Cage,Black, 10-Speed, Medium Cage,Black, 10-Speed, Medium Cage,Black, 10-Speed, Medium Cage,Black, 10-Speed, Medium Cage,Black, 10-Speed, Medium Cage

[subtitle] => Black, 10-Speed, Medium Cage,Black, 10-Speed, Medium Cage,Black, 10-Speed, Medium Cage,Black, 10-Speed, Medium Cage,Black, 10-Speed, Medium Cage,Black, 10-Speed, Medium Cage

)

 

All the data seems to be correct, but I can't figure out why the same data has been grabbed over and over for price,brand and subtitle.

[mikosiko]

this is one of those very common cases here where the signature of our good friend mjdamato apply perfectly:

The quality of the responses received is directly proportional to the quality of the question asked.

 

the solution offered to you apply and solve perfectly the example and objectives that you addressed in your first post, obviously if you changed the conditions the solution may or may not be correct.

 

MORAL: post your real tables, describe your real problem and objectives and we could help you better.

[fenway]

You're looking to produce a "pivot table".

[sloth456]

Ok here are my real tables, obviously not all my rows, because that would be massive.

 

catalog_product_entity:

 

Contains my product SKU's and an entity_id which is used to relate to other tables.  I want to select all rows WHERE type_id=simple (there are other types not shown here)

 

entity_identity_type_idattribute_set_idtype_idskuhas_optionsrequired_optionscreated_atupdated_at

7 4 10 simple 30019472 0 0 2011-11-17 13:15:13 2011-11-26 13:11:19

8 4 10 simple 30019471 0 0 0000-00-00 00:00:00 2011-11-26 13:11:19

9 4 10 simple 30019470 0 0 2011-11-17 13:15:13 2011-11-26 13:11:19

10 4 10 simple 30019469 0 0 0000-00-00 00:00:00 2011-11-26 13:11:20

11 4 10 simple 30019468 0 0 0000-00-00 00:00:00 2011-11-26 13:11:21

 

 

catalog_product_entity_decimal:

 

Once I have selected all products and there ids I need to go to this table  and pull out value WHERE entity_id=catalog_product_entity.id AND attribute_id=67 (price) AND store_id=0 (there are other rows not shown here)

 

value_identity_type_idattribute_idstore_identity_idvalue

21 4 67 0 7 209.95

22 4 72 0 7 500

 

catalog_product_entity_varchar:

 

SELECT value WHERE entity_id=catalog_product_entity.id AND attribute_id=134 (subtitle) OR attribute_id=146 (brand) AND store_id=0

 

value_identity_type_idattribute_idstore_identity_idvalue

234 4 134 0 7 Black, 10-Speed, Medium Cage

235 4 146 0 7 Sram

 

cataloginventory_stock_item:

 

This is a table with a lot of columns that aren't important to us.  We just want to select  'qty' WHERE product_id=catalog_product_entity.id

 

item_id product_id stock_id qty min_qty use_config_min_qty backorders use_config_backorders min_sale_qty use_config_min_sale_qty max_sale_qty use_config_max_sale_qty is_in_stock low_stock_date notify_stock_qty use_config_notify_stock_qty manage_stock use_config_manage_stock stock_status_changed_auto use_config_qty_increments qty_increments use_config_enable_qty_inc enable_qty_increments

7 7 1 1 0 1 0 0 1 1 0 1 1 NULL 0 1 0 1 0 1 0 1 0

8 8 1 1 0 1 0 0 1 1 0 1 1 NULL 0 1 0 1 0 1 0 1 0

 

 

The tables are too large to post in their entirety, so if you want to inspect further I can provide sql exports for these tables.

 

Thanks again for all your help so far.