CODEWITHSUNDEEP
mysql
Manish Malviya
Manish Malviya

Reputation: 871

How to write mysql query for fetching data from 2 sql query?

I have 2 tables

tbltraining 

trId(PK)   trname 
-------    ------
1         training1 
2         training2
3         training3
4         training4
5         training5
6         training6


tbltrainAssign 

    trassigno    trId(FK)      date1           date2
    ---------    ------     -----------     ----------
    1            1          12/12/2012      12/12/2013
    1            1          12/12/2012      12/12/2013
    2            3          01/12/2012      08/12/2013
    2            3          01/12/2012      08/12/2013
    2            3          01/12/2012      08/12/2013
    3            1          02/12/2012      12/12/2013
    3            1          12/12/2012      12/12/2013

what i want is the data from tbltraining with the count of assignments. trId =1 is assigned 2 times and trId is assigned 1tim in tbltrainAssign . so the result should look like

trId        trName       count
1          training1     2
2          training2     0
3          training3     1
4          training4     0
5          training5     0
6          training6     0

Upvotes: 0

Views: 92

Answers (3)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79969

What you need is a simple, straightforward, JOIN especially LEFT JOIN to get unmatched trNames that had no assignment in the second table, with COUNT and a GROUP BY like so:

SELECT t.trId, t.trname, COUNT(s.trId)
FROM tbltraining t
LEFT JOIN tbltrainAssign s ON t.trId = s.trId
GROUP BY t.trId, t.trname;

SQL Fiddle Demo

Side not: Please avoid descriptive prefixes that you are using in the tables' names and the columns' names the tbl and the tr.

Update: You have to select the distinct dates from the assignment table in order to count the distinct assignments for each training like so:

SELECT t.trId, t.trname, COUNT(s.trId)
FROM tbltraining t
LEFT JOIN
(
  SELECT DISTINCT trId, DATE(date1), date(date2)
  FROM tbltrainAssign
) s ON t.trId = s.trId
GROUP BY t.trId, t.trname;

Updated SQL Fiddle Demo

Upvotes: 2

Boris Gappov
Boris Gappov

Reputation: 2493

select t.trId, t.trname, count(t.trassigno) from (
    select tb.trId, tb.trname, ta.trassigno from tbltrainAssign ta
    inner join tbltraining tb on ta.trId=tb.trId
) t
group by t.trId, t.trname

Upvotes: 0

Shamis Shukoor
Shamis Shukoor

Reputation: 2515

    select a.trId trId,a.trName trName,count(distinct b.trssigno) count 
    from tbltraining a, tbltrainAssign b 
    where a.trId=b.trId 
    group trId,trName 
    order by trId

this will give the desired result

Upvotes: 1

Related Questions