SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 1999
    Location
    Los Angeles, CA
    Posts
    73
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Extracting the result of a WHERE clause calculation?

    Here's my query:

    Code:
     
    SELECT
    b.name AS name,
     
    FROM
    biz AS b, zipcodes AS z
     
    WHERE
    (POW((69.1*(z.lon-$lon) * cos($lat/57.3)),2) +
    POW((69.1*(z.lat-$lat)),2)) < ($radius*$radius)
    AND b.zip = z.zip
     
    ORDER BY 'score' DESC
    $lon, $lat come from a zipcode table and $radius from a form.


    So the query above gives me 'name'. Is there a way to also get the resulting number that comes out of that WHERE calculation? To understand what I mean, this is what I want to be able to do:

    Code:
     
    WHERE
    (POW((69.1*(z.lon-$lon) * cos($lat/57.3)),2) +
    POW((69.1*(z.lat-$lat)),2)) AS distance < ($radius*$radius)
    In other words, assign to 'distance' the result of that calculation so that when I fetch the rows, I can display both the 'name' and the 'distance'.

    Btw, this is my first mysql database. So there is probably a smarter way of doing this.
    Last edited by anis; Dec 30, 2004 at 22:03.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT b.name AS name
         , (POW((69.1*(z.lon-$lon) * cos($lat/57.3)),2)
           +POW((69.1*(z.lat-$lat)),2)) as distance
      FROM biz AS b
    inner
      join zipcodes AS z
        on b.zip = z.zip
     WHERE (POW((69.1*(z.lon-$lon) * cos($lat/57.3)),2) 
           +POW((69.1*(z.lat-$lat)),2)) < ($radius*$radius)
    ORDER 
        BY distance DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Dec 2004
    Location
    in my mind. well, physically in the Bay Area
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    you've almost answered your own question

    hi,
    you can add it to your select statement. (if you wanted to specify an integer or string to return with all results you can do that as well, ie. SELECT b.name AS name, 'behrouz' AS myFavDJ). of course i can't see the numbers you are working with to make sure it is accurate, but try this:

    SELECT
    b.name AS name, POW(69.1*(z.lon-$lon) * cos($lat/57.3),2) +
    POW(69.1*(z.lat-$lat),2) AS distance

    FROM
    biz AS b, zipcodes AS z

    WHERE
    (POW((69.1*(z.lon-$lon) * cos($lat/57.3)),2) +
    POW((69.1*(z.lat-$lat)),2)) < ($radius*$radius)
    AND b.zip = z.zip

    ORDER BY 'score' DESC

    i think that's all...

  4. #4
    SitePoint Enthusiast
    Join Date
    Jul 1999
    Location
    Los Angeles, CA
    Posts
    73
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937, thanks. Two questions: What's a good place to learn about inner and left joins with explanations on why and when to use them? And secondly, in the query you posted, say I want to add more WHERE conditions, where would I put them? For example, say there is a relational table (bizmake) that has the biz_id and make_id fields. So in my old query I would've had additionally:

    FROM ........, bizmake AS bm
    WHERE ............ AND bm.biz_id = b.biz_id AND bm.make_id = $makeid

    How do I insert these new conditions into the query you posted?


    Thanks for the help, much appreciated.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    there are a number of tutorials (and other resources) listed here: SQL Links
    Code:
    SELECT b.name AS name
         , (POW((69.1*(z.lon-$lon) * cos($lat/57.3)),2)
           +POW((69.1*(z.lat-$lat)),2)) as distance
      FROM bizmake AS bm
    inner
      join biz AS b
        on bm.biz_id = b.biz_id
    inner
      join zipcodes AS z
        on b.zip = z.zip
     WHERE bm.make_id = $makeid
       and (POW((69.1*(z.lon-$lon) * cos($lat/57.3)),2) 
           +POW((69.1*(z.lat-$lat)),2)) < ($radius*$radius)
    ORDER 
        BY distance DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast
    Join Date
    Jul 1999
    Location
    Los Angeles, CA
    Posts
    73
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What if there are multiple relational tables, like bizmake AS bm, and say make AS m, and the following needs to be true also: bm.biz_id = b.biz_id AND bm.make_id = m.make_id.

    Can there be multiple conditions after the ON in an INNER JOIN? Like
    INNER JOIN bizmake AS bm
    ON bm.biz_id = b.biz_id, bm.make_id = m.make_id

    How do you choose which table to put after the FROM and which ones to put in the INNER JOIN when the SELECT is from multiple tables, like SELECT b.biz_id, b.name, z.city, z.state, POW(...), etc

    And finally =) why not just use a WHERE clause with multiple ANDs? Are the INNER JOINs more efficient or faster?

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by anis
    What if there are multiple relational tables, like bizmake AS bm, and say make AS m, and the following needs to be true also: bm.biz_id = b.biz_id AND bm.make_id = m.make_id.
    just keep adding INNER JOIN and ON clauses

    Quote Originally Posted by anis
    Can there be multiple conditions after the ON in an INNER JOIN? Like
    INNER JOIN bizmake AS bm
    ON bm.biz_id = b.biz_id, bm.make_id = m.make_id
    sure, except with ANDs and/or ORs where you have that comma

    Quote Originally Posted by anis
    How do you choose which table to put after the FROM and which ones to put in the INNER JOIN when the SELECT is from multiple tables, like SELECT b.biz_id, b.name, z.city, z.state, POW(...), etc
    depends if you're mixing INNER with OUTER joins, it matters, because OUTER joins keep certain rows whether there are matching rows or not, but with INNER, sequence doesn't matter

    Quote Originally Posted by anis
    And finally =) why not just use a WHERE clause with multiple ANDs? Are the INNER JOINs more efficient or faster?
    for 3 reasons, which i can go into in detail some other time:

    1. cleaner, self-documenting, easier to understand, maintain
    2. can only do outer joins with OUTER JOIN syntax
    3. JOIN syntax is the sql standard
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Enthusiast
    Join Date
    Jul 1999
    Location
    Los Angeles, CA
    Posts
    73
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There's one thing that confuses me when doing the JOINs, and that's which table(s) do you put after the FROM if you are SELECTing from multiple places. Like in your first reply to me, you but FROM biz AS b, and then on your second reply you put FROM bizmake AS bm and moved the biz to an INNER JOIN.

    Here's the query that does everything correctly, and I am having trouble putting INNER JOINs in it.

    Code:
    SELECT
    	b.biz_id AS id, b.name AS name, b.zip AS zip,
    	r.$tp_score AS score, r.$tp_numposts AS numposts,
    	z.city AS city, z.state AS state, (POW...) AS distance
    
     
    FROM
       biz AS b, rating AS r, zipcodes AS z, bizmake AS bm, make AS m
    
     
    WHERE
       (POW(....) < ($radius*$radius) AND
       b.zip = z.zip AND
       r.biz_id = b.biz_id AND
       bm.biz_id = b.biz_id AND
       bm.make_id = m.make_id AND
       m.make = '$make' AND
       r.$tp_score > 0.00 AND
       r.$tp_numposts > 0
       ORDER BY 'score' DESC

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    for inner joins, it doesn't really matter what sequence you put them in, put them into whatever order makes sense, and if possible, put the table(s) with very discriminating filter condition first

    for example,
    Code:
    select b.biz_id AS id
         , b.name AS name
         , b.zip AS zip
         , r.$tp_score AS score
         , r.$tp_numposts AS numposts
         , z.city AS city
         , z.state AS state
         , (POW...) AS distance
      from make AS m
    inner
      join bizmake AS bm
        on m.make_id 
         = bm.make_id
    inner
      join biz AS b     
        on bm.biz_id 
         = b.biz_id
    inner
      join rating AS r
        on b.biz_id 
         = r.biz_id
    inner
      join zipcodes AS z
        on b.zip 
         = z.zip 
     where m.make = '$make'
       and (POW(....) < ($radius*$radius) 
       and r.$tp_score > 0.00 
       and r.$tp_numposts > 0
    order 
        by score desc
    the condition m.make = '$make' restricts the m rows to only one, so that makes more sense to me to start there, whereas the conditions r.$tp_score > 0.00 and r.$tp_numposts > 0 are probably true for most r rows, so the join condition is not so restrictive

    the part that "makes sense" to me is that it is more efficient to keep only a small number of rows while joining one table to the next, rather than starting with many rows and then throwing only a few away
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Enthusiast
    Join Date
    Jul 1999
    Location
    Los Angeles, CA
    Posts
    73
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the query. So why does it make sense to keep only a small number of rows while joining one table to the next instead of starting with many rows and throwing a few away? Maybe because I am a beginner to SQL, but to me it looks clearer and makes more sense to have a SELECT with multiple WHEREs. Your query (since its the first time I've seen it like that) is a bit confusing, but I am starting to understand it.

    Does MySQL first process and gather the tables after the FROM and then go after the WHERE or does it go after the INNER JOIN ON clauses?

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    a good database optimizer will take the WHERE conditions into consideration to decide which tables to search in which sequence

    using JOIN syntax is preferable because it is more explicit

    don't forget reason #2 from above, you can only do outer joins with OUTER JOIN syntax

    once you start seeing tables being joined either as INNER or as OUTER, the syntax is more consistent
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    by the way, thank you for asking those nice questions

    i can see that you are making great progress, and you will probably master sql completely by the end of the year

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

  13. #13
    SitePoint Enthusiast
    Join Date
    Jul 1999
    Location
    Los Angeles, CA
    Posts
    73
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    And I am kinda surprised SP isn't paying you to roam the forums =) Thanks for the help.


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
  •