MMDE Posted February 10, 2012 Share Posted February 10, 2012 The title is a bit misleading, but it's basically what I want to do. Let me explain, but before I do so the version of MySQL that I want to use this query on is 5.0.67. I think what I want to do is best explained by showing an example. ----------------------------------------------------- | table1 | ----------------------------------------------------- | t1id(int)(p key) | name(string) | active(boolean) | ----------------------------------------------------- | 1 | name1 | 1 | ----------------------------------------------------- | 2 | name2 | 0 | ----------------------------------------------------- | 3 | name3 | 1 | ----------------------------------------------------- ----------------------------------------------------------------------------------- | table2 | ----------------------------------------------------------------------------------- | t2id(bigint)(p key) | t1id(int) | time(int) | value(double) | current(boolean) | ----------------------------------------------------------------------------------- | 1 | 1 | 1328848200 | 33.5 | 0 | ----------------------------------------------------------------------------------- | 2 | 2 | 1328848201 | 44.2 | 0 | ----------------------------------------------------------------------------------- | 3 | 3 | 1328848202 | 23.5 | 0 | ----------------------------------------------------------------------------------- | 4 | 3 | 1328848210 | 10.7 | 0 | ----------------------------------------------------------------------------------- | 5 | 2 | 1328848212 | 15.3 | 1 (may be 0) | ----------------------------------------------------------------------------------- | 6 | 1 | 1328848215 | 33.9 | 0 | ----------------------------------------------------------------------------------- | 7 | 3 | 1328848230 | 17.4 | 0 | ----------------------------------------------------------------------------------- | 8 | 1 | 1328848245 | 12.1 | 0 | ----------------------------------------------------------------------------------- | 9 | 1 | 1328848246 | 15.7 | 1 | ----------------------------------------------------------------------------------- | 10 | 3 | 1328848255 | 12.2 | 0 | ----------------------------------------------------------------------------------- | 11 | 3 | 1328848270 | 22.5 | 1 | ----------------------------------------------------------------------------------- ----------------------------- | result | ----------------------------- | t1id | time | value | ----------------------------- | 1 | 1328848245 | 15.7 | ----------------------------- | 3 | 1328848255 | 12.2 | ----------------------------- To simplify it a little bit: [*]Filter every row in table2 that has the same t1id as a table1 where in table1 the row has active=false. [*]Filter every row in table2 that has current=true in table2. [*]Filter every row in table2 where another row has the same t1id and a higher time value. [*]SELECT t1id, time, value. Very important that t1id and value is from the same row as time I understand I could probably have put it all in the same table, but I don't want to do that, and I'm still left with the problem that I'm wondering about. I know I have to use some sub-query, and it would probably be some INNER JOIN, but I'm not exactly sure how to write it. thanks in advance Quote Link to comment Share on other sites More sharing options...
MMDE Posted February 10, 2012 Author Share Posted February 10, 2012 Because I did a typo and can't edit it anymore, I must post a new post... EDIT: ----------------------------- | result | ----------------------------- | t1id | time | value | ----------------------------- | 1 | 1328848245 | 12.1 | ----------------------------- | 3 | 1328848255 | 12.2 | ----------------------------- As you can see, I did a typo in the result table, which would really confuse anyone trying to understand this. Quote Link to comment Share on other sites More sharing options...
fenway Posted February 10, 2012 Share Posted February 10, 2012 Yeah, a simple join should do it. Quote Link to comment Share on other sites More sharing options...
MMDE Posted February 10, 2012 Author Share Posted February 10, 2012 Yeah, a simple join should do it. If it's that simple, please show me, because I've been trying to do this for a while, and I've used for example LEFT JOIN many times before. Would really really appreciate it. Quote Link to comment Share on other sites More sharing options...
fenway Posted February 12, 2012 Share Posted February 12, 2012 What do you have thus far? Quote Link to comment Share on other sites More sharing options...
MMDE Posted February 17, 2012 Author Share Posted February 17, 2012 I thought I posted it... hmm, might have been lost when the site went down for a little while? SELECT t1id, time, value FROM table1 LEFT JOIN table2 ON table1.t1id = table2.t1id WHERE active = true AND current = false ORDER BY time DESC LIMIT 1 Now this would get me more than one row with the same t1id. I could potentially do a workaround in PHP while I fetch the data into arrays, but it might become very heavy if there is a lot of entries in table 2. Is there any way to remove the "duplicate" rows, kinda GROUP BY them, but without using GROUP BY, because GROUP BY wouldn't give me the row with the highest time value in table2? Quote Link to comment Share on other sites More sharing options...
kickstart Posted February 17, 2012 Share Posted February 17, 2012 Hi I am not 100% sure what you are trying to get, as you seem to be saying to filter for something, then your SQL filters them out if that is the case. However to get the latest item you need a subselect for that to JOIN on as well. Also as you need matching rows on both tables you can use an INNER JOIN rather than an OUTER JOIN. Something like this, although you will have to check it is really what you want:- SELECT table1.t1id, table2.time, table2.value FROM table1 INNER JOIN table2 ON table1.t1id = table2.t1id AND table1.active = true INNER JOIN (SELECT t1id, MAX(time) AS MaxTime FROM table2 WHERE current = false GROUP BY t1id) SubQuery ON table2.t1id = SubQuery.t1id AND table2.time = SubQuery.MaxTime All the best Keith Quote Link to comment Share on other sites More sharing options...
MMDE Posted February 18, 2012 Author Share Posted February 18, 2012 Thank you, that worked well, and I was able to tweak it to everything I needed to use it for! It probably also gave me a better understanding of INNER JOIN, and so I might just use it more later! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.