none
Sql and LINQ query conversion RRS feed

  • Question

  • I have following table

    columns in tblvv

    ID MID VName Speed Name

    columns in tblrv

    ID FFID OName regno RID

    columns in tblre

    RegionID Region StartDate EndDate


    here ID in tblrvv and MID in tblvv is same

    i want Vname according to region 

    sample data is 

    in tblvv

    ID    MID	       VName
    1         1	       DPSB
    2         1	       MV
    3         1            MV
    4         2            dp3sb
    5         2            RV
    6         3            RV
    7         3            ASB
    8         3            ASB
    9         3            dpsb
    10        3             MV


    tblrv

    ID	FFID	OwnerName	RegNo       RID
    1	10366	  Max	          AJ-29      1
    2	10444	  John	          AV-99       1
    3	10454	  Pete	          AL-95       2
    4       1053      david            PO-99      3
    5        10788    Eagl              UI-98     3
    6        10876    Rainb             QY-17     4

    tblre

    RID	           Region	      StartDate         EndDate
    1	            UK	              2014-02-01 	2014-02-28 
    2	            US	             2014-02-01 	2014-02-28 
    3	            Paris	     2014-02-01 	2014-02-28
    4	            london	     2015-02-03          2014-02-10 
    5                   france           2015-03-01         2014-03-07 
    6                    denmark         2016-03-14 	2014-03-21 
    7                    india           2016-04-01 	2014-04-07 


    now when i run this query

    select * from tblre WHERE RID=1
    SELECT * FROM tblrv  where ID=1
    SELECT * FROM tblvv  where MID=1


    table re

    RegionID	Region	        StartDate	EndDate
    1	      UK	       2014-02-01 	2014-02-28 

    table rv
    then this return like this

    ID	FFID	OwnerName	  RegNo
    1	10366	Max	           AJ-29




    and table vv

    ID	MasterID	VName
    2	1	            MV
    3	1	            DPSB
    4       1                   MV

    NOW I Want data like this

    vnametotal           RegionID   Vname
          1                1        DPSB
          2                1        MV


    MEANS there is 1 DPSB in RID 1 and 2 MV in RID 1 as check in tblrvv

    What I have tried:

    i try this query

    select count(vname) as total,tblre.RID,Vname from tblvv
    join tblrv on tblrv.ID=tblvv.MID
    join tblre on tblre.RID = tblrv.RID
    
    group by 
    VName,
    tblre.RID



    BUT this query not show me correct result



    Saturday, June 25, 2016 8:07 AM

Answers

  • Hi rebmaamberrebmaamber,

    >> BUT this query not show me correct result

    It seems that you need where condition on your SQL statement, like this:

    select count(vname) as total,tblre.RID,Vname from tblvv
    join tblrv on tblrv.ID=tblvv.MID
    join tblre on tblre.RID = tblrv.RID
    where tblvv.MID = 1
    group by 
    VName,
    tblre.RID

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, June 27, 2016 8:45 AM
    Moderator