Extracting the result of a WHERE clause calculation?

[anis]

Here's my query:

 
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:

 
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.

[r937]

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

[supernacular]

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...

[anis]

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.

[r937]

there are a number of tutorials (and other resources) listed here: SQL Links

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

[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.

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?

[r937]

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

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

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

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

[anis]

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.

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

[r937]

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,

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

[anis]

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?

[r937]

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

[r937]

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

[anis]

And I am kinda surprised SP isn't paying you to roam the forums =) Thanks for the help.