Thread: Help with mysql

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    8
    Rep Power
    0

    Help with mysql


    I need to create a report on some data for power levels for mobiles.

    For each area I need to extract the number of samples which the SQL below does. Now I need to add another 3 tables. Also to extract the number of samples greater -86 from each table.

    I have looked at joins and unions but it is a bit confusion can anybody point me in the right direction.

    TBLNOTSPOTS holds the boundaries of the sample area with a unique id. The other tables hold the lat, lng and power level for each operator and generation (2G / 3G).

    SELECT CELLID, COUNT( V.RSCP )
    FROM TBLVF3G V
    RIGHT OUTER JOIN TBLNOTSPOTS N ON V.LAT BETWEEN N.LAT1 AND N.LAT3
    AND V.LNG BETWEEN N.LNG3 AND N.LNG1
    WHERE
    N.SID =5
    GROUP BY N.CELLID ASC

    Any help or guidence would be great.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,027
    Rep Power
    4210
    not sure what kind of "point in the right direction" you're looking for in regard to joins... do you want links to tutorials?

    also, you should use INNER JOIN, not RIGHT OUTER JOIN (because of your WHERE condition)
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    8
    Rep Power
    0
    The reason for the outer join is that not all boundaries can be covered so I need to know when there are no values recorded.

    data in each table is referenced by a siteid (SID) this returns the boundry from tblnotspots and lat/lng and power (rscp).

    So I thought I needed the outer join to reference all cellid's that had no rows returned but only for the siteid.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,027
    Rep Power
    4210
    Originally Posted by Dazzsoap
    The reason for the outer join is that not all boundaries can be covered so I need to know when there are no values recorded.
    you know what? you were right, sorry

    i guess i'm too used to criticising left outer joins with where conditions on the right table

    your query is fine
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    8
    Rep Power
    0
    Do I need to add more joins for the other tables using the same criteria and add the additional columns in the select statement.

    This is the part I am unsure of.

    joining multiple tables.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,027
    Rep Power
    4210
    multiple joins are a breeze

    the only trouble you will encounter comes if there are multiple 1-to-many relationships

    if you describe the primary and foreign keys of all the tables involved, i'll mock up the query for you
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    8
    Rep Power
    0
    Many thanks
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    8
    Rep Power
    0
    This is my attempt at the joins but the values returned are incorrect. As you can see I have repeated the joins.

    Maybe I should be using subqueries but not sure.

    SELECT CELLID, COUNT( V.RSCP ) AS VF, COUNT(TEF.RSCP) TEF, COUNT(H.RSCP) H3UK
    FROM TBLNOTSPOTS N
    LEFT JOIN TBLVF3G V
    ON V.LAT BETWEEN N.LAT1 AND N.LAT3
    AND V.LNG BETWEEN N.LNG3 AND N.LNG1
    AND V.SID = 5
    LEFT JOIN TBLTEF3G TEF
    ON TEF.LAT BETWEEN N.LAT1 AND N.LAT3
    AND TEF.LNG BETWEEN N.LNG3 AND N.LNG1
    AND TEF.SID = 5
    LEFT JOIN TBLH3UK3G H
    ON H.LAT BETWEEN N.LAT1 AND N.LAT3
    AND H.LNG BETWEEN N.LNG3 AND N.LNG1
    AND H.SID = 5
    WHERE N.SID = 5
    GROUP BY N.CELLID ASC
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,027
    Rep Power
    4210
    Originally Posted by Dazzsoap
    This is my attempt at the joins but the values returned are incorrect. As you can see I have repeated the joins.

    Maybe I should be using subqueries but not sure.
    you, sir, are an amazingly fast learner, i think you have a knack for sql that you should nurture

    yes, some strategy is needed to prevent the cross join effect of the multiple 1-to-many relationships that i suspected -- this effect is characterized by numbers blown way up

    probably the easiest approach is to split the counts into a UNION query
    Code:
    SELECT n.cellid
         , 'vf' AS typ
         , COUNT(*) AS cnt
      FROM tblnotspots n
    LEFT OUTER
      JOIN tblvf3g v 
        ON v.sid = n.sid
       AND v.lat BETWEEN n.lat1 AND n.lat3
       AND v.lng BETWEEN n.lng3 AND n.lng1 
     WHERE n.sid = 5
    GROUP 
        BY n.cellid 
    UNION ALL
    SELECT n.cellid
         , 'tef' AS typ
         , COUNT(*) AS cnt
      FROM tblnotspots n
    LEFT OUTER
      JOIN tbltef3g tef 
        ON tef.sid = n.sid
        ON tef.lat BETWEEN n.lat1 AND n.lat3
       AND tef.lng BETWEEN n.lng3 AND n.lng1 
     WHERE n.sid = 5
    GROUP 
        BY n.cellid 
    UNION ALL
    SELECT n.cellid
         , 'h3uk' AS typ
         , COUNT(*) AS cnt
      FROM tblnotspots n
    LEFT OUTER
      JOIN tblh3uk3g h 
        ON h.sid = n.sid
        ON h.lat BETWEEN n.lat1 AND n.lat3
       AND h.lng BETWEEN n.lng3 AND n.lng1 
     WHERE n.sid = 5
    GROUP 
        BY n.cellid 
    ORDER
        BY cellid
         , typ
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    8
    Rep Power
    0
    Thanks for the code I gather where I had counts of 0 and now 1 is due to the count including the cellid in the count ?
  20. #11
  21. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,027
    Rep Power
    4210
    Originally Posted by Dazzsoap
    I gather where I had counts of 0 and now 1 is due to the count including the cellid in the count ?
    damn, i am batting oh-fer in this thread

    yes, please change COUNT(*) in the query with COUNT(v.sid) or COUNT(tef.sid) or COUNT(h3uk.sid) as the case may be

    i am such a numpty
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    8
    Rep Power
    0
    Originally Posted by r937
    damn, i am batting oh-fer in this thread

    yes, please change COUNT(*) in the query with COUNT(v.sid) or COUNT(tef.sid) or COUNT(h3uk.sid) as the case may be

    i am such a numpty
    r937 thanks for all your help and assistance. Apologise for delay in replying different timezones and sleep got in the way.

IMN logo majestic logo threadwatch logo seochat tools logo