CODEWITHSUNDEEP
mysql
Michael Emerson
Michael Emerson

Reputation: 1813

mySQL JOIN statement with COUNT not matching correctly

I am trying to write a mySQL statement that selects data from one table but counts up entries from another table with a matching ID in a specific field.

The two tables are jobs and job_cards. A job will always be a single entry which will have multiple job cards, so I need to write a singular statement that selects data from the job table but adds another field in the result which is a count of all related job cards.

Example:

jobs table:

|  ID  |  customer | status | date_added |
|----------------------------------------|
|   1  |     3     | active | 2017-10-10 |
------------------------------------------

job_cards table is a bit more complex but includes a column called job_id which will be 1 in this case. But lets say there are 3 cards assigned to the job above. I wrote the following statement:

SELECT j.*, COUNT(jc.id) AS card_count FROM jobs j LEFT JOIN job_cards jc ON j.id = jc.job_id

But the count column only returns the TOTAL number of cards in the job_cards table, regardless of which job they are assigned to. Not only that, but it only ever returns a single result even though at the moment there are 4 entries in the jobs table.

Is there any way to do what I need to do with a single statement?

EDIT:

Sample data from the job_cards table:

|  ID  |  job_id  | customer | description | materials | notes |
|--------------------------------------------------------------|
|   1  |     1    |     3    |  blah blah  |  none     |  test |
|   2  |     1    |     3    | something   |  pipes    |  n/a  |
----------------------------------------------------------------

The result I would like to get is:

|  ID  | customer | date_added | card_count |
|-------------------------------------------|
|   1  |     3    | 2017-10-10 |      2     |
---------------------------------------------

Where the ID here is the ID of the job.

Upvotes: 0

Views: 51

Answers (1)

nacho
nacho

Reputation: 5397

You can try this:

SELECT *, (select count(*)
           from job_cards jc
           where jc.job_id=j.id) as card_count 
FROM jobs j

Upvotes: 1

Related Questions