Two identical query types on identical type tables, but different output

Question

In the context of my previous question, I asked a few hours ago: -

Extract multiple similar rows for each individual column type

Some helpful guys gave me a reasonable solution to my problem. I matched the solution in my database table, which is almost the same as the dummy table I developed for this question. But, unfortunately, the query gives the wrong output in my source table. I believe this is a quick fix, and I cannot detect it.

The solution given to me by some useful SO users: -

http://sqlfiddle.com/#!2/0472b/1

After I displayed the solution: -

http://sqlfiddle.com/#!2/082d9/1

The correct output for my table would be: -

+---+--------------+-------------------------------+--------------+-------------+-----+ | d | Johnny Bravo | August, 27 2013 00:00:00+0000 | NAB | Same Amount | 150 | +---+--------------+-------------------------------+--------------+-------------+-----+ | d | Courage Dog | August, 27 2013 00:00:00+0000 | Commonwealth | Same Amount | 150 | +---+--------------+-------------------------------+--------------+-------------+-----+ 

This question is more like bitmaps :D

Answer 1

When I saw the original solution, I thought: “What if there are duplicates in columns 2 and 3”. So here, here is the problem. Here is a revised query that works on your SQL script:

 SELECT ht.* FROM helper_table ht INNER JOIN (SELECT `date`, `amount` FROM helper_table ht GROUP BY `date`, `amount` HAVING COUNT(distinct `name`) = (SELECT COUNT(DISTINCT `name`) FROM helper_table) ) da ON ht.`date` = da.`date` AND ht.`amount` = da.`amount`; 

This still assumes that the date and amount fields do not contain NULL values ​​(or at least the NULL values ​​do not match).