SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Query INNER JOIN for 3 differents tables?

    Hello everyone, I need your help.

    This is my tables in db mysql:

    doTbl_A
    Code:
    ID	idL		Prv
    1	XX2049201	LT
    2	XX7047001	CH
    3	XX4046407	RM
    4	XX7057306	TE
    5	XX6041804	CB
    doTbl_B
    Code:
    ID	Prv	Sog
    1	LT	7
    2	CH	5
    3	RM	13
    4	TE	7
    5	CB	5
    doTbl_c
    Code:
    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:

    Code:
    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:

    Code:
    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:

    Code:
    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. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,399
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by cms9651 View Post
    This is my query:

    Code:
    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:

    Code:
    strCOUNT	Prv		IDL		Sog
    1		LT		XX2049201	7
    2		CH		XX7047001	5
    1		RM		XX4046407	13
    1		TE		Xx7057306	7
    3		CB		XX6041804	5
    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:

    Code:
    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. #3
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    No it's not. Your query only returns the strCOUNT column.

    Just add another INNER JOIN, and then select the fields you need.
    I don't not add another INNER JOIN... can you help me?

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,399
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    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. #5
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    GREAT Guido !

    Code:
    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. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by cms9651 View Post
    This is my query:

    And this is the output:
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Sorry !

    Code:
    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. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    would it be possible to remove the dreaded, evil "select star" please?

    thank you

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Ok

    Code:
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •