Query INNER JOIN for 3 differents tables?

Databases
1

Hello everyone, I need your help.

This is my tables in db mysql:

doTbl_A


ID	idL		Prv
1	XX2049201	LT
2	XX7047001	CH
3	XX4046407	RM
4	XX7057306	TE
5	XX6041804	CB

doTbl_B


ID	Prv	Sog
1	LT	7
2	CH	5
3	RM	13
4	TE	7
5	CB	5

doTbl_c


ID	CodL		cl_MT	cl_BT
1	XX2049201	2	2783
2	XX7047001	3	1176
3	XX4046407	7	843
4	XX7057306	17	1207
5	XX6041804	6	3743

This is my query:


SELECT COUNT(*) AS strCOUNT
FROM doTbl_A A 
INNER JOIN dotbl_B CA ON CA.Prv = A.Prv 
GROUP BY A.Prv 
ORDER BY A.Prv ASC

And this is the output:


strCOUNT	Prv		IDL		Sog
1		LT		XX2049201	7
2		CH		XX7047001	5
1		RM		XX4046407	13
1		TE		Xx7057306	7
3		CB		XX6041804	5

I need this output, any help would be very much appreciated.
Many thanks to any who can help me out with this:


strCOUNT	Prv		IDL		Sog	cl_MT	cl_BT
1		LT		XX2049201	7	2	2783
1		CH		XX7047001	5	3	1176
1		RM		XX4046407	13	7	843
1		TE		Xx7057306	7	17	1207
1		CB		XX6041804	5	6	3743

Thanks in advance.
Chevy

2

No it’s not. Your query only returns the strCOUNT column.

I need this output, any help would be very much appreciated.
Many thanks to any who can help me out with this:


strCOUNT	Prv		IDL		Sog	cl_MT	cl_BT
1		LT		XX2049201	7	2	2783
1		CH		XX7047001	5	3	1176
1		RM		XX4046407	13	7	843
1		TE		Xx7057306	7	17	1207
1		CB		XX6041804	5	6	3743

Thanks in advance.
Chevy

Just add another INNER JOIN, and then select the fields you need.

3

I don’t not add another INNER JOIN… can you help me?

4

Do you understand how your current query works? There’s already an INNER JOIN there. Do you understand it?
Just add another one for the third table, using the third table name and the fields that join the two tables.

http://dev.mysql.com/doc/refman/5.1/en/join.html

5

GREAT Guido !


SELECT COUNT(*) AS strCOUNT
FROM doTbl_A A 
INNER JOIN dotbl_B CA ON CA.Prv = A.Prv 
INNER JOIN dotbl_C CB ON CB.CODL = A.IDL
GROUP BY A.Prv 
ORDER BY A.Prv ASC
6

i would just like to point out that your query does ~not~ produce that output

your query produces only one column, whereas your “output” shows 4 columns

you should be grateful that guido was able to understand what you’re doing

ordinarily SQL requires more rigour than an approximate description of what’s going on

7

Sorry ! :sick:


SELECT COUNT(*) AS strCOUNT,
A.*, CA.*, SUM(CB.cl_MT) AS tot_MT, SUM(CB.cl_BT) AS tot_BT
FROM doTbl_A A 
INNER JOIN dotbl_B CA ON CA.Prv = A.Prv 
INNER JOIN dotbl_C CB ON CB.CODL = A.IDL
GROUP BY A.Prv 
ORDER BY A.Prv ASC
8

would it be possible to remove the dreaded, evil “select star” please?

thank you

:slight_smile:

9

Ok :slight_smile:


SELECT COUNT(*) AS strCOUNT,
A.Prv, A.IDL, 
CA.Prv, CB.CODL,
SUM(CB.cl_MT) AS tot_MT, SUM(CB.cl_BT) AS tot_BT
FROM doTbl_A A 
INNER JOIN dotbl_B CA ON CA.Prv = A.Prv 
INNER JOIN dotbl_C CB ON CB.CODL = A.IDL
GROUP BY A.Prv 
ORDER BY A.Prv ASC