CODEWITHSUNDEEP
mysqlsqljoinsubquery
user1995997
user1995997

Reputation: 591

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

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.

Upvotes: 12

Views: 35223

Answers (2)

BhavikKama
BhavikKama

Reputation: 8800

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

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79969

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.

Upvotes: 18

Related Questions