Jump to content

query multiple tables...


acsonline
 Share

Recommended Posts

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?

Link to comment
Share on other sites

This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.