Two tables and special output

Databases
1

Hi all, I need your help.

This two tables in DB MySQL:

_table1:


Mat		app
a006829		MN
a006840		HP
a006842		MN
a006846		HP
a261469		AI
a261469		MN
a261469		QT
a427300		QT
a427300		QT
a427300		SF

_Table2


ID	Mat		DU
1	a248649		IR
2	a252938		TU
3	a258357		TU
4	a426320		IR
5	a426622		IR
6	a426702		IR
7	a427300		IR
8	a261444		TU
9	a261469		TU
10	a261488		TU

I need this output, can someone help me? Thanks in advance.


DU	app	q	Mat
IR	QT	2	a427300
IR	SF	1	a427300
TU	QT	1	a261469
TU	MN	1	a261469
TU	AI	1	a261469
2

Join the two tables, do a COUNT(*) and a group by.

3 
SELECT two.du
     , one.app
     , COUNT(*) AS q
     , one.mat
  FROM _table1 AS one
INNER
  JOIN _table2 AS two
    ON two.mat = one.mat
GROUP
    BY two.du
     , one.app
     , one.mat

a simple inner join with a count

4

Very good! many thanks!