Inner join query using multiple values

Question

My table structure is as shown:

table:App
|AppID|AppName|AppType|
   1    test     new

table:AppRelease
|AppReleaseID|AppID|ReleaseDate|ReleaseVersion|
   1            1   2012-06-20        2.2
   2            1   2012-06-19        2.3

I write a query as shown below:

SELECT A.*,B.ReleaseDate,B.ReleaseVersion 
FROM App as A 
  LEFT JOIN AppRelease as B ON A.AppID=B.AppID

This query is working for single value in AppRelease table, but multiple values in AppRelease table I want to get last added value. Is it possible in single query?

if you need "last added value" you should try to add in the end - ORDER BY B.ReleaseDate — shershen
– shershen, Commented Jun 19, 2012 at 19:55
Answer added, please tell me whether it works or not. I'm currently adding a sqlfiddle, remembering Sir @FahimParkar previous recommendations... — Sebas
– Sebas, Commented Jun 19, 2012 at 20:40
@Sebas : Also you can provide sqlfiddle if table is small or OP has provided inputs. Its easy to understand OP and especially OTHERS. — Fahim Parkar
– Fahim Parkar, Commented Jun 19, 2012 at 20:43
unfortunately the sqlfiddle says the having clause is incorrect (unkown column releasedate) which is really unprobable. Adding link for you guys to check — Sebas
– Sebas, Commented Jun 19, 2012 at 20:46

Sebas · Accepted Answer · 2012-06-19 22:30:24Z

4

SELECT aa.*, bb.AppReleaseID, bb.ReleaseDate
FROM App aa LEFT JOIN (
            SELECT a.AppID, a.AppReleaseID, a.ReleaseDate
            FROM AppRelease a INNER JOIN (
                        SELECT AppID, MAX(ReleaseDate) mx FROM AppRelease 
                        GROUP BY AppID
                    ) b ON a.AppID = b.AppID AND a.ReleaseDate = b.mx
        ) bb ON bb.AppID = aa.AppID

fiddle: http://sqlfiddle.com/#!2/befa2/3

edited Jun 19, 2012 at 22:30

answered Jun 19, 2012 at 20:32

Sebas

21.6k9 gold badges60 silver badges109 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Jake Feasel Over a year ago

sqlfiddle.com/#!2/befa2/9 - compare with this. I removed your innermost having clause, as I'm not sure that makes sense (and mysql was complaining about it). Doesn't this version work?

Sebas Over a year ago

the having would ensure the extracted date was the last one. With your version all are returned, which is wronmg

Sebas Over a year ago

hmm, after thinking, the result is the same, but the inner calculations are not. Many rows are grouped and the max is extracted, being the same each time. In my version, only the rows with the max will be returned, which is probably faster. But now I'm not sure of anything anymore...

Sebas Over a year ago

are you following me or what? :-)) that's funny I was thinking that I could leave you a message about this one today

Jake Feasel Over a year ago

I'm not following you - just keeping tabs on how people are using my site :) Particularly makes my ears perk up when there may be a problem with it (which I thought might be the case here). Now I just think that the having clause syntax you're using is simply incorrect.

|

richardthepratt · Accepted Answer · 2012-06-19 20:05:27Z

1

Using a JOIN I think the best you can do is select the maximum values from AppRelease.

SELECT A.*,MAX(B.ReleaseDate),MAX(B.ReleaseVersion)
FROM App as A
LEFT JOIN AppRelease as B ON A.AppID=B.AppID
GROUP BY A.AppID

If you want to semantically get the last-added value, you would probably be better off using subqueries, such as

SELECT A.*,
(SELECT B.ReleaseDate FROM AppRelease as B
WHERE B.AppID=A.AppID ORDER BY B.AppReleaseID DESC LIMIT 1)
as ReleaseDate,
(SELECT B.ReleaseVersion FROM AppRelease as
B WHERE B.AppID=A.AppID ORDER BY B.AppReleaseID DESC LIMIT 1)
as ReleaseVersion
FROM App as A

answered Jun 19, 2012 at 20:05

richardthepratt

1255 bronze badges

3 Comments

Mihai Stancu Over a year ago

Isn't the second option much less optimized by the db engine?

richardthepratt Over a year ago

I would imagine so, yes. As I said, it's more a matter of semantics. The first query is simpler and gets the job done, but technically it's not getting the last-added value, it's getting the greatest value. They have the same result in Suresh's tables, but that's only because this table structure by design has ever-increasing values.

Mihai Stancu Over a year ago

What i meant was that subqueries called in JOIN clauses are run exactly once, the ones called in WHERE clauses are called once per row, and those called as COLUMN values are called once per row multiplied by the number of column values constructed via subqueries.

Mihai Stancu · Accepted Answer · 2012-06-19 20:18:57Z

1

To achieve this in a single query you need to obtain the maximum value first in a subquery:

SELECT A.*,B.ReleaseDate,B.ReleaseVersion 
FROM App as A 
    LEFT JOIN AppRelease as B ON A.AppID = B.AppI
WHERE B.ReleaseDate = (
    SELECT MAX(ReleaseDate)
    FROM AppRelease
    WHERE AppID = A.AppID GROUP BY AppID
    LIMIT 0, 1
) OR B.ReleaseDate IS NULL;

I think there's another way to do this by using the subquery as a join table.

edited Jun 19, 2012 at 20:18

answered Jun 19, 2012 at 19:58

Mihai Stancu

16.2k2 gold badges35 silver badges51 bronze badges

2 Comments

Mihai Stancu Over a year ago

@Sebas I haven't tested it and at the time of your comment i didn't specify the WHERE/GROUP clauses so it wasn't obtaining the correct values. But aside from that is there another reason it won't work?

Sebas Over a year ago

now that you filtered by appid in the subquery, this is one reason less to not work. But still, you are adding an inclusive filter on the table B, which is implicitely turning the left join to an inner join.

Collectives™ on Stack Overflow

Inner join query using multiple values

3 Answers 3

7 Comments

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related