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 (txn_id, brand).

My goal/intention now is to make this happen with the actual data, which is split into 2 tables, 1_txns and 2_products,, which have a common field (sku).

Query #1: Queries 1 table, returns desired results. (top 10 brands which share a transaction id with :brand

SELECT brand
FROM transactions
id IN (SELECT id FROM transactions WHERE brand = :brand)
AND brand <> :brand
GROUP BY brand

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` 

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

If Adidas is the selected brand

If Reebok is the selected brand
Something that will deal with a tie and still list both in alphabetical order
  • what is the purpose in: '2_products'.'brand' <> :brand? Just taking a brief look, it seems to me that, that would cause you to not get the results you're looking for. Dec 10, 2015 at 18:37
  • This is so that the selected brand itsself is omitted from the results. Dec 10, 2015 at 18:38
  • 1
    Your query is basically logically equivalent to "where 0<>0". The list in the IN clause where 2_products.brand=:brand is never going to be the same set as and 2_products.brand <> :brand as they are logically opposite. It will always return 0 rows
    – Philᵀᴹ
    Dec 10, 2015 at 18:46
  • @Phil So does that imply that a better way to perform this query would be to create a temporary table? as 1_txns.txn_id, 2_products.brand FROM 1_txns LEFT JOIN 2_products on ...sku=sku..... and then just perform the first query on that ? The transactions table has 3.5 million rows, and I'm only testing on a t2.micro amazon rds. Would creating a temp table be too hardware intensive? Dec 10, 2015 at 18:55
  • Can you provide the table schema of 1_txns and 2_products? It appears 1_txns.txn_id is not unique, and can be associated with multiple skus? Dec 10, 2015 at 19:25

1 Answer 1


this query is supposed to return a list of the top 10 brands (other than the selected brand) that are purchased with the selected brand.

Try this:

SELECT `2_products`.`brand`, COUNT(*) as numOrders
FROM `2_products`
INNER JOIN `1_txns` ON `1_txns`.sku=`2_products`.sku
INNER JOIN (SELECT `1_txns`.`txn_id` 
    FROM `1_txns`
    INNER JOIN `2_products` USING (sku)
    WHERE brand = :brand) as tmp_txns ON tmp_txns.txn_id=`1_txns`.txn_id
WHERE brand <> :brand
GROUP BY brand
ORDER BY numOrders desc, brand ASC

Example sqlfiddle

  • Thanks Derek, it's a step closer, but this is returning the top 10, omitting the selected brand and regardless of whether they are bought with the selected brand. Just the top 10 brands by number of orders Dec 10, 2015 at 21:09
  • You're right, sorry. Fixed it to be an INNER JOIN and the correct join condition...Try now? Dec 10, 2015 at 21:31
  • Excellent!!!!! Thanks very much. Can you point me in the direction of any resources that can help me learn about joins and relationships between tables? Definitely something I need to learn, is what different types of joins do compared to one another. Dec 10, 2015 at 21:38
  • @AdamCopley dba.se is a good place to browse questions like that. For this specific one, does this help you? dba.stackexchange.com/a/156/180 Dec 10, 2015 at 22:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.