Geek asks and answers

Problem with MySQL INNER JOIN query containing subquery in it

I have two tables, categories 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'

What are the results in

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

Problem

I want the URL of the last line added here, but as in the first line, instead of url = foo2.jpg and max (ID) = 3, this leads to foo.jpg. I can not understand the problem in the request.

I use max (ID) to get the last row, which gives the correct last row for max (ID), but not the corresponding url column.

+5
source share
2 answers

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

, GROUP BY category MAX(ID) , url, category, GROUP BY, MySQL . .

, JOIN category images, images , MAX(id) GROUP BY category images. images , max id= id.

id.

+8

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)

, ... sql... . http://sqlfiddle.com/#!2/5fe63/36

,

+1

More articles:


All Articles