2

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 <> :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

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
  • 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. – agpoweredmg Dec 10 '15 at 18:37
  • This is so that the selected brand itsself is omitted from the results. – Adam Copley Dec 10 '15 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 '15 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? – Adam Copley Dec 10 '15 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? – Derek Downey Dec 10 '15 at 19:25
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
LIMIT 10;

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 – Adam Copley Dec 10 '15 at 21:09
  • You're right, sorry. Fixed it to be an INNER JOIN and the correct join condition...Try now? – Derek Downey Dec 10 '15 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. – Adam Copley Dec 10 '15 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 – Derek Downey Dec 10 '15 at 22:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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