MySQL Query for getting last row on the basis of category

I have the following table structure:

Category
-------------------
id
slug
values
category
sort


Incidents
-------------------
id
scid
title
impact
date
servicestatus
incidentsstatus
details
createdon
editedon

In in incidents table we have multiple entries for a single category. scid is the category id which is given in table category (id)

I want to show all the category names with the value of servicestatus field from incidents table.

Kind of

Service      Status
-------    ----------
Internet     1 
Email        0
Server1      1

Please check and advise what we can do with it, I tried it with join between both table on the basic of category.id and insidents.scid but that is showing dup开发者_开发知识库licate results?

Thanks!

---

Use this query :

select cat.value, ins.servicestatus from Incidents ins, category cat where ins.scid = cat.id group by cat.id;

---

If I understand it correctly, the column "servicestatus" will contain 0 or 1? or?

If it is to see if there any "open" incidents you could make your SQL as this

SELECT 
    c.category, max(i.servicestatus) 
FROM 
    Category as c 
INNER JOIN 
    Incidents as i ON c.id = i.scid 
GROUP BY 
    c.category

The MAX() sql command will return the highest value from the incidents table rows within each category

\T

---

If you want to see current status from the incident table, you should use something like this:

select c.Category, i.servicestatus 
from category as c inner join incidents as i on c.id = i.scid 
where i.date = (select max(date) from incidents as i2 where i.scid = c.id)