Join query returning 0 rows - impossible where 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
WHERE
id IN (SELECT id FROM transactions WHERE brand = :brand)
AND brand &lt;&gt; :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` &lt;&gt; :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


2_products

(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
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 &lt;&gt; :brand
GROUP BY brand
ORDER BY numOrders desc, brand ASC
LIMIT 10;


Example sqlfiddle