In mysql, how do I select ONLY every row that has the same value in one column but different ones in another?

Question

I'm not really a back-end developer so I have very little experience with sql. I need to filter from a table the "monthly buyings", where a client would buy in the same transaction products to be shipped in different dates.

Let's say I have this on a table:

transactionID |   deliveryDate  
------------- |   ------------
  1           |    2013-11-24  
  1           |    2013-11-24  
  2           |    2013-11-26   
  3           |    2013-11-10  
  3           |    2013-11-17  
  3           |    2013-11-24  
  4           |    2013-11-10  
  4           |    2013-11-10  
  4           |    2013-11-17  
  4           |    2013-11-17

I want this as a result:

transactionID |   deliveryDate  
------------- |   ------------
  3           |    2013-11-20  
  3           |    2013-11-22  
  3           |    2013-11-24  
  4           |    2013-11-10  
  4           |    2013-11-10  
  4           |    2013-11-17  
  4           |    2013-11-17

Transaction 3 and 4 are the only ones that fit my criteria, because transaction 1, whereas there are multiple buyings in the same transaction, the delivery date is the same. Transaction 2 doesn't fit either I need to have multiple items being bought. So, basically I need to select every row where the count of the transactionID is more than one, but only where there is also more than one distinct deliveryDate.

What I tried so far is this:

SELECT *
FROM myTable
WHERE `transactionID` IN
    (     SELECT `transactionID`
          FROM `myTable`
          GROUP BY `transactionID`
          HAVING COUNT(*) > 1
    )
ORDER BY `transactionID`

I know I'm just filtering when there is more than one buy in the same transaction, but I don't really know how to follow this. Besides, since there are plenty of more columns this query is pretty slow, so I think that there is probably a better direction where some of you can point me to.

Thanks!

Answer 1

You are close. You need to count the distinct dates:

SELECT *
FROM myTable
WHERE `transactionID` IN
    (     SELECT `transactionID`
          FROM `myTable`
          GROUP BY `transactionID`
          HAVING COUNT(distinct DeliveryDate) > 1
    )
ORDER BY `transactionID`