Having issue with MySQL INNER JOIN query having sub-query in it

Question

I have two tables, category and images. Here, Category.ID == Images.Category

Category

-----------------------
| ID | parent | name  |
-----------------------
| 1  | 1      | foo   |
| 2  | 1      | bar   |
| 3  | 2      | lorem |
-----------------------

Images

--------------------------------------
| ID  | category    | url            |
--------------------------------------
| 1   | 1           | foo.jpg        |
| 2   | 2           | bar.jpg        |
| 3   | 1           | foo2.jpg       |
--------------------------------------

I tried MySQL Query

SELECT *
FROM `category`
INNER JOIN
(SELECT MAX(ID) , url, category FROM `images` GROUP BY `category`)
AS a ON category.ID = a.category
WHERE `parent` = '1'

Which Results in

-------------------------------------------
| ID | parent | name  | url     | max(ID) |
-------------------------------------------
| 1  | 1      | foo   | foo.jpg | 3       |
| 2  | 1      | bar   | bar.jpg | 2       |
-------------------------------------------

The Problem is

I want url of last added row in here, but as in first row, Instead of url = foo2.jpg and max(ID) = 3, it results in foo.jpg. I can't figure out the problem in query.

I use max(ID) for getting last row, which gives correct last row for max(ID) but not appropriate url column.

Solution 1

Try this instead:

SELECT *
FROM `category`   AS c
INNER JOIN images AS i ON i.category = c.id
INNER JOIN
(
    SELECT  category, MAX(ID) AS MAXId
    FROM `images` 
    GROUP BY `category`
)AS a  ON i.category = a.category
      AND i.ID       = a.MaxID
WHERE c.`parent` = '1';

SQL Fiddle Demo

The problem is that, you were GROUP BY category inside the subquery and select MAX(ID) , url, category which were not included in an aggregate function nor in the GROUP BY clause, so MySQL picks up an arbitrary value for these columns. Thats why you were getting in consistent results.

To solve this, JOIN the two tables category and images normally, then add an extra join between the table images and a subquery which compute the MAX(id) with GROUP BY category for the same table images. Then join this subquery with the table images on the join condition that the max id = to the original id.

This will give you only those image details for the last id.

Solution 2

In a Shorter way

SELECT c.id,c.parent,c.name, MAX(c.ID) , url, category
    FROM `category` c
    INNER JOIN Images i on c.id=i.id and 
     c.id=(select max(id) from category)

I think this query will work for you..tested with sql fiddle...with this link. http://sqlfiddle.com/#!2/5fe63/36

let me know if solved

Comments

[Mahmoud Gamal]

@user1995997 - You're welcome any time. See my edit for more info about this issue.

[user1995997]

Now I understood very well. Thanks for such great response.

[user1995997]

Connection between both table is Category.ID == Images.Category. Yes this pattern will works too, thanks for the answer.