Reputation: 864
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
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
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
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
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
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
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