MySQL Question (joins)

I'm not that into MySQL joins, so maybe you could give me a hand. I've got the following tables:

Table a  
Fields ID,name

Table b  
Fields aID,cID,ID,found

Table c  
Fields ID,name

The result I want to get is the following: I want all the records where b.found = 1. Of these records I don't wa开发者_如何学运维nt a.id or a.name, but I want the number of records that would have been returned if I would have wanted so. So if there are five records that have b.found = 1 and c.id = (for example) 3, then I want a returned value of 5, c.id and c.name.

Someone is able to do this?

Actually this is what I want to get from the database: A list of all records in table C and a count of records in table B that has found = 1 and b.c_id = c.id

---

Table: a
Fields: ID, name

Table: b
Fields: aID, cID, found

Table: c
Fields: ID, name



SELECT c.ID, c.name, COUNT(1)
FROM b
JOIN c ON c.ID = b.cID AND b.found=1
GROUP BY c.ID

---

SELECT c.id, c.name, COUNT(*)
    FROM c
        INNER JOIN b
            ON c.id = b.c_id
                AND b.found = 1
    GROUP BY c.id, c.name

---

SELECT COUNT(*), c.id, c.name FROM a, b, c WHERE a.id = b.a.id AND c.id = b.a.id AND b.found = 1 AND c.id = idThatIAmSearchingFor

Apologies if I didn't get the syntax exact, but I believe that's the basic structure you want. The COUNT function returns the number of rows found by the query.

---

Something like:

SELECT count(`c`.*), 
           `c`.`id`, 
          `c`.`name` 
  FROM `b` 
  JOIN `c` 
    ON `c`.`id` = `b`.`c_id` 
 WHERE `b.found` = 1

---

I think this would provide the required output -

select count(*), b.cID, c.name from b
inner join c on c.id=b.cID and b.found=1
group by b.cID

---

SELECT COUNT(*) AS Count, c.id, c.name FROM b join a on a.id = b.a_id WHERE b.found = 1 GROUP BY c.Id;

COUNT returns count of records in each group from GROUP BY.