MYSQL innerjoin with limit?

Question

I have two tables, one of the holds tools, the other one holds the tools history like location and who has it. I need to run a search question on this tables and have tried this

SELECT tblmaskin.* 
FROM `tblmaskin` 
INNER JOIN tblmaskinhistorik ON tblmaskin.maskinId=tblmaskinhistorik.maskinId 
WHERE tblmaskin.maskin='".$_POST['sok']."' 
OR tblmaskinhistorik.var='".$_POST['sok']."'
 OR  tblmaskinhistorik.anvandarnamn='".$_POST['sok']."'

But the problem is that I only want to search the last inserted row for that tool in the history table. When you do a search for a tool by place for example you only want to get the tool that is there now.

Is there a way to do that?

SQL Fiddle example.

possible duplicate of http://stackoverflow.com/questions/12600527/accessing-last-inserted-row-in-mysql — Harry, Oct 20 '14 at 10:04
http://stackoverflow.com/questions/3619030/mysql-join-the-most-recent-row-only — Praveen Prasannan, Oct 20 '14 at 10:11

score 1 · Accepted Answer · edited May 23 '17 at 12:11

A side note: It looks you're using PHP and you're not using prepared statement, check out PDO or mysqli to avoid SQL injection.

There's actually an article in official MySQL docs about this + similar thing was already solved here and here.

You need to run a sub-query to fetch id of the "latest" history record (assuming record with max id is the right one) and then use that id to fetch correct record.

Note: I've replaced your inlined php variables with PDO's named placeholders

SELECT tblmaskin.* 
FROM `tblmaskin` 
-- Select record from tblmaskinhistorik with the highest id
INNER JOIN (SELECT maskinId, MAX(maskinhistId) AS maxid
           FROM tblmaskinhistorik
           GROUP BY maskinId) AS t_max
    ON t_max.maskinId = tblmaskin.maskinId
-- Joining whole row
INNER JOIN tblmaskinhistorik ON t_max.maxid=tblmaskinhistorik.maskinhistId
WHERE tblmaskin.maskin=:sok 
    OR tblmaskinhistorik.var=:sok
    OR tblmaskinhistorik.anvandarnamn=:sok

Run EXPLAIN on the query to ensure that it's working efficiently.

Not working, if i do a search on a user tblmaskinhistorik.anvandarnamn i don't get all the tools that user have right now and i also get other users tools. Thanx for trying i might just cheat and add two fields in the tool table that contains the last user and place. But it really irritates me I hate giving up. — user2978137, Oct 20 '14 at 11:11
@user2978137 it should be `INNER JOIN tblmaskinhistorik ON t_max.maxid=tblmaskinhistorik.maskinhistId` (wrong id checked), try it out; I'm adding sqlfiddle example to your question. Also please add expected results. — Vyktor, Oct 20 '14 at 11:40

score -1 · Answer 2 · answered Oct 20 '14 at 10:05

-1

insert limit 1 order by 'your field' desc with your query

answered Oct 20 '14 at 10:05

Jobayer

1,221
1
14
22

why it's negatively marked? I am confused what's wrong with it? – Jobayer Oct 20 '14 at 10:09
1

OP wants to fetch one line for each tool not one line in general. – Vyktor Oct 20 '14 at 10:20

MYSQL innerjoin with limit?

2 Answers2