gistfile1.txt

Here's my sample data set

mysql> SELECT tf.id, tf.authorization_id_response, tf.tran_type FROM transaction_feeds tf;
+-----+---------------------------+-----------+
| id  | authorization_id_response | tran_type |
+-----+---------------------------+-----------+
| 239 | 223672                    | 9120      | 
| 240 | 223672                    | 9220      | 
| 241 | 223673                    | 9120      | 
+-----+---------------------------+-----------+
3 rows in set (0.00 sec)


I'd like to be write a query that will find all items in the table with
tran type 9120 which do not also have another entry in the table with the 
same authorization_id_response.  My naive first guess was:

  SELECT tf.*, COUNT(tf.id) AS related_item_count FROM transaction_feeds tf 
  WHERE tran_type="9120" GROUP BY authorization_id_response\G

However, the restriction in the WHERE knocks out item 240, so related_items_count 
for 239 is 1 instead of 2.  If I drop the where clause, I can get only the trans 
I want, but at that point have no way of also filtering them down by tran_type.  
How should I go about writing this query?

------------

gtowey suggested: "yeah, left join on authorization_id_response WHERE t2.tran_type IS NULL"

Sadly, this didn't work for me:

mysql> SELECT t.authorization_id_response, t2.authorization_id_response, t.tran_type FROM 
transaction_feeds t LEFT JOIN transaction_feeds t2 ON 
t.authorization_id_response=t2.authorization_id_response WHERE t2.tran_type IS NULL\G
Empty set (0.00 sec)

--------------

Further, gtowey responded: "jadams: ah, in the ON clause add AND t2.tran_type!=t.tran_type"

Again, sadly,

mysql> SELECT t.authorization_id_response, t2.authorization_id_response, t.tran_type FROM 
transaction_feeds t LEFT JOIN transaction_feeds t2 ON 
t.authorization_id_response=t2.authorization_id_response AND t2.tran_type!=t.tran_type 
WHERE t2.tran_type IS NULL\G
Empty set (0.00 sec)