PHP / MySQL query

Question

After days of failing I hope that someone more skilled can help me with a solution.

I have two tables, one containing stocks and the other stock values. Please, you do not have to comment on field types etc as this is not a production development, I am only trying to get a grasp on join and mysql alias.

-- Stocks table:

DROP TABLE IF EXISTS `stocks`;
CREATE TABLE `stocks` (
  `stock_id` int(11) NOT NULL AUTO_INCREMENT,
  `stock_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`stock_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;


-- Sample records:

INSERT INTO `stocks` VALUES ('1', 'HighTech');
INSERT INTO `stocks` VALUES ('2', 'NanoTech');
INSERT INTO `stocks` VALUES ('3', 'DotCom');
INSERT INTO `stocks` VALUES ('4', 'NewBiz');


-- Values table:

DROP TABLE IF EXISTS `vals`;
CREATE TABLE `values` (
  `vals_id` int(11) NOT NULL AUTO_INCREMENT,
  `stock_id` varchar(255) DEFAULT NULL,
  `stock_value` int(11) DEFAULT NULL,
  PRIMARY KEY (`vals_id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

-- Sample records:

INSERT INTO `vals` VALUES ('1', '1', '50');
INSERT INTO `vals` VALUES ('2', '1', '700');
INSERT INTO `vals` VALUES ('3', '1', '540');
INSERT INTO `vals` VALUES ('4', '3', '15');
INSERT INTO `vals` VALUES ('5', '3', '44');
INSERT INTO `vals` VALUES ('6', '1', '60');
INSERT INTO `vals` VALUES ('7', '2', '10');
INSERT INTO `vals` VALUES ('8', '3', '53');

There could be 100s of stocks and 1000s of value records.

What I want to do is to print each stock together with a single (latest) stock value. For stock number 3 I want to echo "DotCom" and the latest value "53", none of the others values.

Questions asking for code must **demonstrate a minimal understanding of the problem being solved**. Include attempted solutions, why they didn't work, and the expected results. See also: [Stack Overflow question checklist](http://meta.stackexchange.com/questions/156810/stack-overflow-question-checklist). — John Conde, Apr 19 '14 at 15:50
@JohnConde: [should-stack-overflow-be-awarding-as-for-effort](http://meta.stackexchange.com/questions/210840/) — juergen d, Apr 19 '14 at 15:52
I am sorry for the lack of information, I was on my mobile phone. I will update the post tomorrow. — Urdar, Apr 19 '14 at 16:53
possible duplicate of [Fetch the row which has the Max value for a column](http://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column) — Sean Vieira, Apr 20 '14 at 02:44

Wit Wikky · Accepted Answer · 2014-04-21T10:11:07.310

0

Oh yeah , your table name values creating problem here , try to change it to someother name like vals or something.it would works/

here it is

 SELECT * FROM stocks S JOIN vals V ON V.vals_id = ( SELECT MAX(vals_id) FROM vals Va WHERE Va.stock_id = S.stock_id )

edited Apr 21 '14 at 10:11

answered Apr 19 '14 at 15:56

Wit Wikky

1,542
1
14
28

I am unable to implement until tomorrow, but this seems to be a proper solution. Thank you very much user3515379! :) – Urdar Apr 19 '14 at 16:50
Back at my computer I tried your code today. It is somewhat similar to what I have tried earlier, and unfortunately with the same (lack of) results. It looks like my xampp server does not approve of using table alias like "..stocks as s.." Really annoying. – Urdar Apr 20 '14 at 18:29
You should use without alias than. – Wit Wikky Apr 20 '14 at 18:34
Believe me, I have tried. Can you see anything wrong here? SELECT stocks.stock_name, values.stock_value FROM stocks INNER JOIN values ON values.stock_id = stocks.stock_id ORDER BY stocks.stock_id DESC – Urdar Apr 20 '14 at 19:28
"values", a reserved word.. Thank you so much for pointing that out, hours waisted learning that the hard way ;) Now I get a list of stocks and their corresponding values (plural). I tried with GROUP BY stocs.stock_id to get only the single newest value, I received the oldest.. Do you have one final tip for me? :) – Urdar Apr 20 '14 at 19:57
Okay,you need to loop through the result to get all the rows `while($row = mysql_fetch_assoc($result)){ //Do stuff }` – Wit Wikky Apr 20 '14 at 20:04
$result contains 8 rows (see sample records above). Is there no way to use LIMIT 1 on vals or something in order to have $result contain only the three newest values? – Urdar Apr 20 '14 at 20:18
Hmmm , try this `select stocks.stock_name,vals.stock_value from stocks , vals where (stocks.stock_id , vals.stock_value) in (select vals.stock_id , max(vals.stock_value) from vals group by vals.stock_id) order by vals.stock_id desc ` – Wit Wikky Apr 20 '14 at 20:43
Never been closer! The results are now showing only one value for each stock, but it's the highest value and not necessarily the latest one (the one with the highest vals_id) Your query is a bit too complex for me to understand right now, hopefully I will understand it and be able to edit it tomorrow. If you feel like checking it again please feel free, if not thank you so much for your effort with this =D – Urdar Apr 20 '14 at 21:33
Hi again. Following an article I made the following solution which seems to work 100 % – Urdar Apr 21 '14 at 09:47
SELECT * FROM stocks S JOIN vals V ON V.vals_id = ( SELECT MAX(vals_id) FROM vals Va WHERE Va.stock_id = S.stock_id ) – Urdar Apr 21 '14 at 09:47

PHP / MySQL query

1 Answers1