I wish to find products with two different criteria.
The code I used first to search one criteria is;
SELECT rel.object_id, rel.term_taxonomy_id, tt.taxonomy, tt.term_id, ts.name FROM df1wrmw_term_taxonomy tt INNER JOIN df1wrmw_term_relationships rel ON tt.term_taxonomy_id = rel.term_taxonomy_id INNER JOIN df1wrmw_terms ts ON tt.term_id = ts.term_id WHERE tt.taxonomy = "pa_1_scale" AND ts.term_id = 400;
This returns all those products (Object_ID) with the attribute “pa_1_scale” and ts.term_id = 400. I can also do this to return all products with product_cat and ts.term_id = 397, using a different WHERE statement
WHERE tt.taxonomy = "product_cat" AND ts.term_id = 397
UNION ALL just combines the two. How do I get SQL to select both these criteria? I know a WHERE statement combining the two criteria will not work as I think that no table row contains both values?
Any help available would be great.
Advertisement
Answer
You can try to use the following that will join duplicated tables with a different variable reference, allowing to combine both queries in one:
SELECT tr.object_id, tr.term_taxonomy_id, tt.taxonomy, t.term_id, t.name, tr2.term_taxonomy_id as term_taxonomy_id2, tt2.taxonomy as taxonomy2, t2.term_id as term_id2, t2.name as name2 FROM df1wrmw_term_relationships tr INNER JOIN df1wrmw_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id INNER JOIN df1wrmw_terms t ON tt.term_id = t.term_id INNER JOIN df1wrmw_term_relationships tr2 ON tr.object_id = tr2.object_id INNER JOIN df1wrmw_term_taxonomy tt2 ON tr2.term_taxonomy_id = tt2.term_taxonomy_id INNER JOIN df1wrmw_terms t2 ON tt2.term_id = t2.term_id WHERE tt.taxonomy = 'pa_1_scale' AND t.term_id = 400 AND tt2.taxonomy = 'product_cat' AND t2.term_id = 397
Or you can use in WordPress the class WPDB
and its methods to get SQL query results in PHP:
global $wpdb; $results = $wpdb->get_results(" SELECT tr.object_id, tr.term_taxonomy_id, tt.taxonomy, t.term_id, t.name, tr2.term_taxonomy_id as term_taxonomy_id2, tt2.taxonomy as taxonomy2, t2.term_id as term_id2, t2.name as name2 FROM {$wpdb->prefix}term_relationships tr INNER JOIN {$wpdb->prefix}term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id INNER JOIN {$wpdb->prefix}terms t ON tt.term_id = t.term_id INNER JOIN {$wpdb->prefix}term_relationships tr2 ON tr.object_id = tr2.object_id INNER JOIN {$wpdb->prefix}term_taxonomy tt2 ON tr2.term_taxonomy_id = tt2.term_taxonomy_id INNER JOIN {$wpdb->prefix}terms t2 ON tt2.term_id = t2.term_id WHERE tt.taxonomy = 'pa_1_scale' AND t.term_id = 400 AND tt2.taxonomy = 'product_cat' AND t2.term_id = 397 "); // Display preformatted raw output echo '<pre>' . print_pr($results, true) . '</pre>';
It should work.