MySQL Query Help, Multiple Tables

Question

I have an application that has had a single table where all my data for this query was stored:

SELECT item, name, address, city, state, zip, latitude, longitude, expiration, photo, (((acos(sin((".$submission->latitude."*pi()/180)) * sin((latitude*pi()/180))+cos((".$submission->latitude."*pi()/180)) * cos((latitude*pi()/180)) * cos(((".$submission->longitude."- longitude)*pi()/180))))*180/pi())*60*1.1515*1.609344) as distance FROM Offers HAVING distance < 150 AND expiration > '$daytime - 6' ORDER BY distance;

Basically the query finds the selected information, performs a calculation to find the distance from $submission->latitude/longitude and sorts by those distances.

Due to some outsourced development work, the select data is now spread out amongst 3 tables: table business: business_id, name, address, city, state, zip

table location: business_id, location_id, latitude, longitude

table offers: business_id, item, expiration, photo

I am having a heck of time getting a query to produce the same results on the split table structure. I have tried JOIN on business_id variations, but seem to get complaints about unknown columns and/or every derived table must have its own alias.

I am by no means a good MySQL query builder, hence I am asking for some pushes in the right direction on how I might approach this.

Thanks in advance.

One more thing, the problem seems to be centered around the distance calculations and getting those to happen in the right place. I am able to do a JOIN no problems on the tables, just cant add the distance calculation. Its almost like I need to do a JOIN, then select from the joined result, just not sure how to do that. — brad
– brad, Commented Jul 8, 2012 at 0:43
Normally HAVING goes into queries that have a GROUP BY clause to refer to result of group calculations. I would not be surprised if you had errors from just that HAVING clause. Try using a WHERE instead and see if that resolves your problem. On another note, I don't see any JOINs in your query. Please write your query with joins and also it would be helpful if you could post the schema of the tables you are using. — akanevsky
– akanevsky, Commented Jul 8, 2012 at 0:58

Jirka Kopřiva · Accepted Answer · 2012-07-08 00:56:41Z

1

You have to simply join your queries

$t1 = business table
$t2 = location tabe
$t3 = offers table

SELECT $t1.*,
       $t2.location_id,($t2.latitude .. compute ),($t2.longitude ... compute),
       $t3.item, $t3.expiration, $t3.photo
FROM $t1
JOIN $t2 on $t1.business_id = $t2.business_id
JOIN $t3 on $t3.business_id = $t1.business_id

answered Jul 8, 2012 at 0:56

Jirka Kopřiva

3,1371 gold badge27 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

cosmoonot · Accepted Answer · 2017-04-25 06:48:00Z

0

This piece of code should do it.

Select b.name, b.address, b.city, b.state, b.zip, c.location_id,     c.latitude, c.longitude, d.item, d.expiration, d.photo
FROM business b LEFT JOIN location c
ON b.business_id = c.business_id
LEFT JOIN offers d
ON b.business_id = d,business_id;

edited Apr 25, 2017 at 6:48

cosmoonot

2,1973 gold badges32 silver badges38 bronze badges

answered Apr 25, 2017 at 4:11

birdog9999

16 bronze badges

Collectives™ on Stack Overflow

MySQL Query Help, Multiple Tables

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related