Simon Simon - 4 months ago 9
SQL Question

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 want 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

Answer
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