CODEWITHSUNDEEP
mysql
brad
brad

Reputation: 25

MySQL Query Help, Multiple Tables

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.

Upvotes: 0

Views: 83

Answers (2)

birdog9999
birdog9999

Reputation: 1

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;

Upvotes: 0

Jirka Kopřiva
Jirka Kopřiva

Reputation: 3089

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

Upvotes: 1

Related Questions