CODEWITHSUNDEEP
mysql
Ethical Rajesh
Ethical Rajesh

Reputation: 35

Join two MySQL Tables and get result from categories

SELECT art.*,arg. FROM rd_articles AS art 
LEFT JOIN rd_argument AS arg ON art.cat=arg.id WHERE art.enabled=1 ORDER BY art.id DESC
LIMIT 10

This is simple join query Article table structure is

ID    cat    Description     Date
 1      1    Abc             08-01-2014
 2      1    Aaa             10-01-2014
 3      2    Abcv            11-01-2014
 4      3    Aaa             12-01-2014
 5      3    Aaa             14-01-2014

Arguments table is

ID   Name
 1    A
 2    B
 3    C

I want pick last updated(Date) one item from each cat. How ?

Upvotes: 0

Views: 54

Answers (2)

Santhosh
Santhosh

Reputation: 1791

SELECT ART.*, ARG.*
FROM ARTICLE AS ART
INNER JOIN RD_AGRUEMENT AS ARG
ON ARG.ID = ART.ID
WHERE (ID, DATE) IN
(SELECT ID, MAX(DATE) FROM ARTICLE GROUP BY ID)

Upvotes: 1

Ed Gibbs
Ed Gibbs

Reputation: 26343

This assumes that the enabled column is in rd_articles:

SELECT art.*, arg.*
FROM (
  SELECT * FROM rd_articles
  INNER JOIN (
    SELECT cat, MAX(date) AS maxdate
    FROM rd_articles
    WHERE enabled = 1
    GROUP BY cat
  ) md ON rd_articles.cat = md.cat AND rd_articles.date = md.maxdate
) art
LEFT JOIN rd_argument AS arg ON art.cat = arg.id

The innermost query gets the maximum date for each category, then joins it to the rd_articles table to get only those rd_articles rows that have the latest date for each article. That becomes the cat alias, which is then left-joined to the arguments table just like in your original query. You can add the LIMIT 10 at the end if needed; I wasn't sure what to do with that.

Note that if there's a tie for a category's latest date, you'll get more than one row for each category. If a tie could happen you'll need to break the tie somehow, for example by using the description or the ID. Let me know if that's the case and I'll update my answer.

Upvotes: 1

Related Questions