CODEWITHSUNDEEP
mysqljoinmultiple-tables
Simon
Simon

Reputation: 5708

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

Upvotes: 0

Views: 143

Answers (6)

Rito
Rito

Reputation: 3298

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

Upvotes: 0

Joe Phillips
Joe Phillips

Reputation: 51200

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

Upvotes: 2

Joe Stefanelli
Joe Stefanelli

Reputation: 135868

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

Upvotes: 1

Aaron Hathaway
Aaron Hathaway

Reputation: 4315

Something like:

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

Upvotes: 0

ssobczak
ssobczak

Reputation: 1855

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.

Upvotes: -1

DGH
DGH

Reputation: 11549

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.

Upvotes: 0

Related Questions