My query is performed from a php web request using a PDO prepared statement.
My desired output is to list the top 10 brands which have the same transaction id as the 'selected' brand
:brand by order of frequency.
I initially started with the first query below on a single table with small sample data consisting of 2 columns
My goal/intention now is to make this happen with the actual data, which is split into 2 tables,
2_products,, which have a common field
Query #1: Queries 1 table, returns desired results. (top 10 brands which share a transaction id with
SELECT brand FROM transactions WHERE id IN (SELECT id FROM transactions WHERE brand = :brand) AND brand <> :brand GROUP BY brand ORDER BY COUNT(*) DESC LIMIT 10;
I tried to modify my existing query to use the actual data, and the result returned 0 rows. I used EXPLAIN, and an Impossible WHERE was noted. Obviously I'm by no means an expert, and this is actually my first go at handling joins, and subqueries for filters, with MySQL. If I can get a grip on things like this, then I will look to move some of my excel->access solutions to web based. Many Thanks in Advance
SELECT `2_products`.`brand` FROM `2_products` RIGHT JOIN `1_txns` ON `2_products`.`sku`=`1_txns`.`sku` WHERE `1_txns`.`txn_id` IN (SELECT `1_txns`.`txn_id` FROM `1_txns` WHERE `2_products`.`brand` = :brand) AND `2_products`.`brand` <> :brand GROUP BY `2_products`.`brand` ORDER BY COUNT(*) DESC LIMIT 10;
EDIT: for Derek Downey
Plain english. this query is supposed to return a list of the top 10 brands that are purchased with the selected brand. The data is as follows: 1_txns
(txn_id, sku, salesamount, various other columns like qty etc) 1, 12345, 5 1, 12346, 10 2, 12345, 5 2, 12347, 10 3, 12345, 15 3, 12346, 30 4, 12346, 20 4, 12347, 10
(sku, brand, various other columns) 12345, Nike 12346, Adidas 12347, Reebok
The desired output is:
If Nike is the selected brand Adidas Reebok If Adidas is the selected brand Nike Reebok If Reebok is the selected brand Something that will deal with a tie and still list both in alphabetical order