CODEWITHSUNDEEP
mysqljoin
rayncorg
rayncorg

Reputation: 993

MYSQL INNER JOIN unexpected result

I'm having a problem on my MySQL Inner join. My INNER JOIN doesn't give me my desired result. I have Table1 that contains the TrackNo only then Table2 contains the details of Table1 per trackNo.

>> Table Structure:

enter image description here

>> SQL Code:

SELECT tr.trackNo AS 'TrackNo',
       trD.Status,
       MAX(trD.DatePosted) AS `Date/Time`
FROM Tracking AS tr
INNER JOIN TrackingDetails AS trD
ON tr.trackNo = trD.trackNo
WHERE tr.ClientID='client01'
AND trD.trackNo IN ('xx000001','xx000002','xx000003')
AND trD.DatePosted IS NOT NULL 
AND trD.Status IN (
                'Received',
                'Logged',
                'Prepped',
                'Analyzed',
                'Reviewed',
                'Final Report',
                'Invoiced')
GROUP BY tr.trackNo
ORDER BY tr.trackNo ASC

Here's the result query above:

enter image description here

As you can see the query result image above is correct except for Status column.Where did I go wrong on my SQL Query? Did I miss something?

>> Desired output:

+----------+----------+---------------------+
|  TrackNo |  Status  |      Date/Time      |
+==========+==========+=====================+
| xx000001 | Logged   | 2015-03-09 17:53:14 |
+----------+----------+---------------------+
| xx000002 | Prepped  | 2014-08-15 17:19:00 |
+----------+----------+---------------------+
| xx000003 | Analyzed | 2014-10-10 11:12:00 |
+----------+----------+---------------------+

Thanks in advance!

Upvotes: 1

Views: 120

Answers (1)

Jens
Jens

Reputation: 69440

This should gives you the correct result:

select * from TrackingDetails as a join (
    SELECT tr.trackNo AS 'TrackNo',
           MAX(trD.DatePosted) AS 'Date_Time'
    FROM Tracking AS tr
    INNER JOIN TrackingDetails AS trD
    ON tr.trackNo = trD.trackNo
    WHERE tr.ClientID='client01'
    AND trD.trackNo IN ('xx000001','xx000002','xx000003')
    AND trD.DatePosted IS NOT NULL 
    AND trD.Status IN (
                    'Received',
                    'Logged',
                    'Prepped',
                    'Analyzed',
                    'Reviewed',
                    'Final Report',
                    'Invoiced')
    GROUP BY tr.trackNo
    ORDER BY tr.trackNo ASC

) as x on x.TrackNo = a.trackNo and a.DatePosted = x.'Date_Time'

If you use group by in mysql it is not specified wich values you get from columns which are not in aggregation function or in the group by statement. In most other DBMS it is not allowed to select such columns.

Upvotes: 1

Related Questions