CODEWITHSUNDEEP
mysqlsql
sarathkm
sarathkm

Reputation: 1426

SQL: Get an article for each category

There are two tables article and category.

  nid | title | status
---+-------------+-------
 1 |           abc |     1
 2 |           ggg |      1
 3 |           kkk |      0
 4 |          rrr |      1
 5 |           fff |      1
 6 |           ggg |      1

Where status = 1 is published.

cid | nid 
---+-------------
 1 |           1 
 2 |           2 
 2 |            3 
 3 |           4 
 1 |           5 
 2 |           6

Now I want to get a one nid for each cid, no double occurrence of cid where status is 1.

Upvotes: 0

Views: 111

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

You can use aggregation:

select c.cid, max(c.nid)
from category c join
     article a
     on c.nid = a.nid
where a.status = 1
group by c.cid;

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 95053

First of all you must decide which nid to show for a cid in case of multiple matches. Let's say you want the maximum nid. Select from category and look up articles for their status. Then aggregate.

select cid, max(nid)
from category
where nid in (select nid from article where status = 1)
group by cid;

Upvotes: 1

Darshan Mehta
Darshan Mehta

Reputation: 30839

You can use GROUP BY with JOIN, e.g.:

SELECT t2.cid, MAX(t2.nid)
FROM table2 t2 JOIN table1 t1 ON t2.nid = t1.nid and t1.status = 1
GROUP BY t2.cid;

Upvotes: 1

Husen
Husen

Reputation: 1587

Try this one.

SELECT DISTINCT cid 
FROM category       AS a1
INNER JOIN article  AS a2 ON a1.nid = a2.nid
WHERE a1.[STATUS] = 1

Upvotes: 0

Related Questions