Mysql – Join query returning 0 rows – impossible where

join;MySQLsubquery

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

Best Answer

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

SUGGESTION #1

You should store the timestamp of the minute and index it

ALTER TABLE mytable ADD COLUMN MinuteTimeStamp AFTER TimeStamp;
UPDATE mytable SET MinuteTimeStamp = TimeStamp - MOD(TimeStamp,60);
ALTER TABLE mytable ADD INDEX MinuteTimeStamp_UniqueKey_ndx (MinuteTimeStamp,UniqueKey);

Then, you can do MIN aggregation on MinuteTimeStamp.

SELECT MinuteTimeStamp,MIN(UniqueKey) UniqueKey
FROM mytable GROUP BY MinuteTimeStamp;

and use it get those records

SELECT B.* FROM
(SELECT MinuteTimeStamp,MIN(UniqueKey) UniqueKey
FROM mytable GROUP BY MinuteTimeStamp) A
INNER JOIN mytable B USING (UniqueKey);

It was tactfully pointed out that triggers would degrade performance

Perhaps doing INSERTs like this may help

INSERT INTO mytable (UniqueKey,TimeStamp,MinuteTimeStamp) VALUES
(
    uniquevalue,
    UNIX_TIMESTAMP(NOW()),
    UNIX_TIMESTAMP(NOW() - INTERVAL SECOND(NOW()) SECOND)
);

SUGGESTION #2

Since you have over 1000 columns (Ugh), perhaps a table of those minute timestamps would be better.

CREATE TABLE MinuteKeys
(
    MinuteTimeStamp INT UNSIGNED NOT NULL,
    UniqueKey INT UNSIGNED NOT NULL,
    PRIMARY KEY (UniqueKey)
    KEY MinuteTimeStamp_UniqueKey_ndx (MinuteTimeStamp,UniqueKey)
) ENGINE=MyISAM;
ALTER TABLE MinuteKeys DISABLE KEYS;
INSERT INTO MinuteKeys SELECT TimeStamp - MOD(TimeStamp,60),UniqueKey FROM mytable;
ALTER TABLE MinuteKeys ENABLE KEYS;

Then, you could use that table for the aggregation

SELECT B.* FROM
(SELECT MinuteTimeStamp,MIN(UniqueKey) UniqueKey
FROM MinuteKeys GROUP BY MinuteTimeStamp) A
INNER JOIN mytable B USING (UniqueKey);

EPILOGUE

Other suggestions are possible but you should really consider normalization of the table

See my post Too many columns in MySQL as to why

Postgresql – Left join not working with sub-query

You could use a CROSS JOIN from score_sort to all the students and then a LEFT join to score:

SELECT st.student_id, 
       AVG(COALESCE(sc.value, default_value)) AS average_score
FROM 
(
    SELECT score.subject_id, subject.name, SUM(score.value) AS score_sum
    FROM score
    JOIN subject on subject.id = score.subject_id
    WHERE subject.name != 'skip me'
    GROUP BY score.subject_id
    ORDER BY score_sum DESC
    LIMIT 5
) AS score_sort AS sort 
  CROSS JOIN student AS st
  LEFT JOIN score AS sc
    ON  sort.subject_id = sc.subject_id 
    AND   st.student_id = sc.student_id
GROUP BY st.student_id ;

A different approach would be to alter your query to count the available scores each student has (in the 5 top subjects):

SELECT score.student_id, 
       (SUM(score.value) + (5 - COUNT(score.subject_id)) * default_value) / 5 
           AS average_score
FROM 
(
    SELECT score.subject_id, subject.name, SUM(score.value) AS score_sum
    FROM score
    JOIN subject on subject.id = score.subject_id
    WHERE subject.name != 'skip me'
    GROUP BY score.subject_id
    ORDER BY score_sum DESC
    LIMIT 5
) AS score_sort
JOIN score ON score_sort.subject_id = score.subject_id 
GROUP BY score.student_id ;

But even with this, you would still not get any result for the students that have no score in any of the 5 top subjects.

Best Answer

Related Solutions

Mysql – query records to fetch by time stamp

SUGGESTION #1

SUGGESTION #2

EPILOGUE

Postgresql – Left join not working with sub-query

Related Question