# Thread: Extracting the result of a WHERE clause calculation?

1. ## 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) +
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) +
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.

2. 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)
ORDER
BY distance DESC```

3. ## 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) +
AND b.zip = z.zip

ORDER BY 'score' DESC

i think that's all...

4. 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. 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)
ORDER
BY distance DESC```

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

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

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

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

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

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

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

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

#### Posting Permissions

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