CODEWITHSUNDEEP
sqlcount
NewbieProgrammer
NewbieProgrammer

Reputation: 864

How to use COUNT on two tables with WHERE clause

Guys I have three tables in SQL database. tblTicketDetail, tblEngineer and tblTicket_Engineer (a junction table for many-to-many relationship). What happens in my app is, when I generate a new ticket, the ticket is assigned to either one, two or three (max) engineers (thus the many-to-many relationship).

Following is the structure of tables :

tblTicketDetail

+----------+---------------+--------+ | TicketID | Desc | Status | +----------+---------------+--------+ | 1 | Description 1 | 1 | | 2 | Description 2 | 0 | | 3 | Description 3 | 1 | +----------+---------------+--------+

tblEngineer

+------------+-------+ | EngineerID | Name | +------------+-------+ | 1 | Tom | | 2 | Harry | | 3 | John | +------------+-------+

tblTicket_Engineer

+----------+------------+ | TicketID | EngineerID | +----------+------------+ | 1 | 1 | | 1 | 2 | | 1 | 3 | | 2 | 1 | | 3 | 1 | | 3 | 2 | +----------+------------+

Now what I want to do is COUNT all TicketID which have the status of 1 and where the EngineerID should be specific (like for example 1). I tried this query, but it generates two counts

SELECT  (
          SELECT COUNT(*) total
          FROM   tblTicketDetail WHERE Status = 1
        ) AS count1,
        (
          SELECT COUNT(*) total
          FROM   tblTicket_Engineer WHERE EngineerID = 1
        ) AS count2

In this case (where EngineerID = 1), the query should generate the count of 2. How should I go about doing that?

Upvotes: 0

Views: 5382

Answers (6)

Wirack
Wirack

Reputation: 81

You could do this 

SELECT COUNT(*) total
FROM   tblTicketDetail a, tblTicket_Engineer b
WHERE  a.TicketID = b.TicketID AND a.Status = 1 AND b.EngineerID = 1

Upvotes: 1

khushbu
khushbu

Reputation: 222

can you please try this query

SELECT COUNT(tblTicketDetail.TicketID) FROM tblTicketDetail 
JOIN tblTicket_Engineer ON  tblTicket_Engineer.TicketID = tblTicketDetail.TicketID    
WHERE tblTicket_Engineer.EngineerID = 1
AND tblTicketDetail.Status = 1

Upvotes: 2

Roberto Reale
Roberto Reale

Reputation: 4317

You may want to use a JOIN:

  SELECT COUNT(*) total
  FROM   tblTicket NATURAL JOIN tblTicket_Engineer NATURAL JOIN tblEngineer
  WHERE  Status = 1 AND EngineerID = 1

In case your database engine does not support multiple NATURAL JOIN clauses, or you don't want to use them, you may resort to an explicit JOIN

  SELECT COUNT(*) total
  FROM   tblTicket INNER JOIN tblTicket_Engineer ON (TicketID)
         INNER JOIN tblEngineer ON (EngineedID)
  WHERE  Status = 1 AND EngineerID = 1

NOTE Do not use NATURAL JOIN in production environments. See this post.

Upvotes: 0

Deo
Deo

Reputation: 82

I think below code will help you

SELECT Count(*) FROM 
tblTicket inner join tblTicket_Engineer on 
(tblTicket.TicketID= tblTicket_Engineer.TicketID)
WHERE  tblTicket.Status = '1' 
AND tblTicket_Engineer.EngineerID = '1'

Upvotes: 2

Dimt
Dimt

Reputation: 2328

You need to create a join on your sub-query to get the ticket status and the sub-query should look like below:

      SELECT COUNT(*) total
      FROM   tblTicket_Engineer
      INNER JOIN  tblTicketDetail ON tblTicketDetail.TicketID = tblTicket_Engineer.TicketID AND tblTicketDetail.Status = 1
      WHERE tblTicket_Engineer.EngineerID = 1

Upvotes: 3

anupkumar
anupkumar

Reputation: 357

Try this ,this may help you. 

SELECT COUNT(*) total 
FROM tblTicketDetail as td, tblTicket_Engineer as te 
WHERE td.Status = 1 
      AND te.EngineerID = 1;

Upvotes: 0

Related Questions