query multiple tables...

[acsonline]

Hey,

 

I have a query running inner joins... which currently works, but I need some more information pulled from other tables...

 

define('WPSC_TABLE_PRODUCT_LIST', "{$wp_table_prefix}wpsc_product_list");
define('WPSC_TABLE_PRODUCTMETA', "{$wp_table_prefix}wpsc_productmeta");
define('WPSC_TABLE_CATREF', "{$wp_table_prefix}wpsc_item_category_assoc");
define('WPSC_TABLE_CATNAME', "{$wp_table_prefix}wpsc_product_categories");


$cf="Linked Products";
$sku = get_post_meta($post->ID, $cf, true);
$array = explode(",",$sku);

$products = array_count_values($array);

foreach($products as $key => $value) {
	$product = $wpdb->get_row("SELECT meta.product_id AS pid, list.name AS name, list.price AS price, retailer.category_id AS catid FROM ".WPSC_TABLE_PRODUCTMETA." AS meta INNER JOIN ".WPSC_TABLE_PRODUCT_LIST." AS list ON ( list.id = meta.product_id ) INNER JOIN ".WPSC_TABLE_SHOPNAME." AS retailer ON ( retailer.product_id = meta.product_id ) WHERE `meta_key` IN ( 'sku' ) AND `meta_value` IN ( '{$key}' ) ORDER BY list.id DESC");
?>

 

But I also need the category name - So I have to cross reference the following...

 

wordpdem_wpsc_product_list

 

id      name

433  itemone

432  itemtwo

431  itemthree

 

wordpdem_wpsc_item_category_assoc

 

id  product_id    category_id

1    433                    1

2    432                    2

3    410                    3

 

wordpdem_wpsc_product_categories

 

id  name

1  Bread

2  Fish

3  Snacks

 

I've now written:

SELECT 
meta.product_id AS pid, 
list.name AS name, 
list.price AS price, 
retailer.category_id AS catid, 
category.name AS catname 

FROM wordpdem_wpsc_productmeta AS meta 
INNER JOIN wordpdem_wpsc_product_list AS list ON ( list.id = meta.product_id ) 
INNER JOIN wordpdem_wpsc_item_category_assoc AS retailer ON ( retailer.product_id = meta.product_id ) 
INNER JOIN `wordpdem_wpsc_product_categories``wordpdem_wpsc_product_list` AS category ON ( retailer.category_id = category.id )
WHERE `meta_key` IN ( 'sku' ) AND `meta_value` IN ( '{$key}' ) ORDER BY list.id DESC 

 

However it fails on line 12 (the Where statement...)

 

Any ideas what I've done wrong?