CODEWITHSUNDEEP
mysqlsqlselectjoin
Doug Fir
Doug Fir

Reputation: 21282

Get row with the minimum date from a joined table but don't show this date?

Here is part of my query:

SELECT
    my1.data AS PAW_ID,
    abc.abc_medium AS abc_Medium,
    my.data AS Trial_Date,
    my2.data AS Upgrade_Date,
    my3.lastmod AS Cancel_Date

There are several joins in between. The result set gives multiple instances of PAW_ID when I would only like one. I know that when the query is joined to another table, abc_emails.def208, then the records where the earliest def208.created row exists are the rows that I need.

So if the query above produced the following duplicate data:

123 | email | 1-1-2014 | 2-1-2014 | 3-1-2014
123 | banner| 4-1-2014 | 2-1-2014 | 3-1-2014

I know that this PAW_ID (123) corresponds to data in abc_emails.def208 where the MIN(def208.created) should return the row with email in it.

def208.created is not in the SELECT part of the query, but I do want to draw upon it. How do I select this value and group by everything else, but keep it hidden? The only thing it is needed for is to get the earliest associated data points to each PAW_ID.

Upvotes: 2

Views: 3380

Answers (2)

AdamMc331
AdamMc331

Reputation: 16720

I believe you can accomplish this using a subquery. You can create a table that has the PAW_ID and the minimum date, and join that with your other tables where the PAW_ID and date matches, that way it would eliminate the rows you don't want. Without knowing your table structure it would be hard to give the exact query but I imagine you could do something like this:

SELECT columnsIWant
FROM tables
JOIN otherTables ON conditions
JOIN (SELECT paw_id, MIN(created) AS minDate
      FROM def208
      GROUP BY paw_id) tempTable
   ON tempTable.paw_id = realTable.paw_id AND tempTable.minDate = realTable.created;

As long as you don't have SELECT tempTable.minDate it shouldn't show up in your result set.

EDIT

Another way instead of using another JOIN is to try putting it in your where clause like this:

SELECT columnsIWant
FROM tables 
JOIN otherTables ON conditions
WHERE realDef208.created = (SELECT MIN(created) FROM tempDef208 WHERE tempDef208.paw_id = realDef208.paw_id);

Upvotes: 2

kbbucks
kbbucks

Reputation: 138

Create a View which selects Distinct(paw_id) & Min(created) from your current data and join the view with your normal table(s) on both the id & date, this will isolate the row you are looking for without having to select the unwanted date.

Upvotes: 1

Related Questions