How to use COUNT on two tables with WHERE clause

Question

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?

score 3 · Accepted Answer · edited Apr 21 '14 at 10:27

3

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

edited Apr 21 '14 at 10:27

NewbieProgrammer

874
2
18
50

answered Apr 21 '14 at 09:59

Dimt

2,278
2
20
26

He doesn't need two counts, so he doesn't need this as the subquery. Although it should otherwise be fine. – Clockwork-Muse Apr 21 '14 at 10:20
This is working as per my requirement! Much appreciated. – NewbieProgrammer Apr 21 '14 at 10:25
@Clockwork-Muse just misunderstood the question as have suggested myself by the provided snippet that the op needs a count for all the tickets with status = 1 and then another count with engineer = 1. You are right there is no need for sub-query. – Dimt Apr 21 '14 at 10:35

Deo · Answer 2 · 2014-04-21T10:36:01.830

2

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'

edited Apr 21 '14 at 10:36

answered Apr 21 '14 at 10:12

Deo

82
5

Yes, although I'd probably move `tblTicket_Engineer.EngineerID = '1'` into the join conditions. – Clockwork-Muse Apr 21 '14 at 10:19
Why are you using JOIN on two different IDs in the same table? `tblTicket_Engineer.EngineerID = tblTicket_Engineer.TicketID`. – NewbieProgrammer Apr 21 '14 at 10:30

score 2 · Answer 3 · edited Apr 21 '14 at 10:44

2

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

edited Apr 21 '14 at 10:44

NewbieProgrammer

874
2
18
50

answered Apr 21 '14 at 10:25

khushbu

222
3
11

This query is also working as per requirement after tweaking a little bit! Thank you for your answer Khushbu! – NewbieProgrammer Apr 21 '14 at 10:43

score 1 · Answer 4 · edited Apr 21 '14 at 10:47

1

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

edited Apr 21 '14 at 10:47

NewbieProgrammer

874
2
18
50

answered Apr 21 '14 at 10:14

Wirack

81
5

You don't _strictly_ need `tblEngineer` for this query. And please, avoid the implicit-join syntax (comma-separated `FROM` clause) in favor of explicit joins. – Clockwork-Muse Apr 21 '14 at 10:18
Working perfectly. Much appreciated!! – NewbieProgrammer Apr 21 '14 at 10:45

score 0 · Answer 5 · edited Apr 21 '14 at 10:21

0

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;

edited Apr 21 '14 at 10:21

Clockwork-Muse

12,806
6
31
45

answered Apr 21 '14 at 09:55

anupkumar

357
4
14
28

Nope, the result is `24` when the above query is executed. – NewbieProgrammer Apr 21 '14 at 09:58
You're heading in the right direction, but you've forgotten one important part - you need to tie engineers to the tickets they're working on! This is why the recommendation is to not use the implicit-join syntax (comma-separated `FROM` clause), in favor of explicitly listing your joins - it tends to make this problem more obvious. – Clockwork-Muse Apr 21 '14 at 10:22

score 0 · Answer 6 · edited May 23 '17 at 10:32

0

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.

edited May 23 '17 at 10:32

Community

1
1

answered Apr 21 '14 at 09:56

Roberto Reale

4,247
1
17
21

Generates an error about using multiple NATURAL JOIN. – NewbieProgrammer Apr 21 '14 at 10:04
Then the problem is not generically related to SQL, but to a specific implementation. Which is your RDBMS? – Roberto Reale Apr 21 '14 at 10:06
1

Personally, I'd avoid the use of `NATURAL JOIN` in any production database, because there are often columns that _shouldn't_ be joined, that should still have the same name - `createdOn` audit timestamp columns, any sort of `status` column, the list goes on. Add in the many times that the columns you may want to join _won't_ have the same name - eg `employeeId` <-> `createdBy` audit column - and it doesn't appear that useful. – Clockwork-Muse Apr 21 '14 at 10:30

How to use COUNT on two tables with WHERE clause

6 Answers6