What I want to do is select everything out of ad_listing that have the same postalcode as what the condition for the postal_codes table depicts (WHERE postal_codes.Latitude >= 42.6145300852 AND postal_codes.Latitude <= 43.3373659148 AND postal_codes.Longitude >= -82.8767447212 AND postal_codes.Longitude <= -81.8945312788)

This is the query I cam up with, but have not had any luck.

SELECT * FROM ad_listing LEFT JOIN postal_codes ON ad_listing.postalcode=postal_codes.postalcode WHERE postal_codes.Latitude >= 42.6145300852 AND postal_codes.Latitude <= 43.3373659148 AND postal_codes.Longitude >= -82.8767447212 AND postal_codes.Longitude <= -81.8945312788

Any sugestions?

Hello Unquick,

To trouble shoot this break it down to 2 queries for testing.

SELECT * FROM ad_listing LEFT JOIN postal_codes ON ad_listing.postalcode=postal_codes.postalcode WHERE postal_codes.Latitude >= 42.6145300852 AND postal_codes.Latitude <= 43.3373659148 AND postal_codes.Longitude >= -82.8767447212 AND postal_codes.Longitude <= -81.8945312788

Becomes

1. SELECT PostalCode FROM postal_codes where postal_codes.Latitude >= 42.6145300852 AND postal_codes.Latitude <= 43.3373659148 AND postal_codes.Longitude >= -82.8767447212 AND postal_codes.Longitude <= -81.8945312788

Did you get any results ? if so then query

2. SELECT * FROM ad_listing where ad_listing.postalcode=postal_codes.postalcode

Did you get any results ?


Reason : You may get postal codes or not from the first table and then there may not be any ads that match them in the second table.

You will have to figure out what to do if the first part or the second part returns an empty result set.

Do some testing with the 2 queries before you use a left join to see if you are getting any results.

Hope this helps.

Thanks cornball

So the following query works like a charm. It outputs the all the postal codes within the latitude range.
SELECT PostalCode FROM postal_codes where postal_codes.Latitude >= 42.6145300852 AND postal_codes.Latitude <= 43.3373659148 AND postal_codes.Longitude >= -82.8767447212 AND postal_codes.Longitude <= -81.8945312788

Result (Snapshot):
+------------+
| PostalCode |
+------------+
| N0M 2L0 |
| N0N 1B0 |
| N0N 1C0 |
| N0N 1E0 |
| N0N 1G0 |
| N0N 1H0 ...|
+------------+

However this one is the one that is giving me the trouble. Nothing returns even though there are rows with postal codes that match those in the result of the above query and that of the postal_codes table.
SELECT * FROM ad_listing, postal_codes where ad_listing.postalcode=postal_codes.postalcode

These are the rows it should return as the postal codes are also in the result of the above query.
+-------+---------+------+-------+------+-------+--------------+-------+--------+----------------+------------+-----------+-----------------+------------+-------+----------+
| id | user_id | make | model | year | price | transmission | doors | milage | dayPhone | nightPhone | cellPhone | address | postalCode | color | saleType |
+-------+---------+------+-------+------+-------+--------------+-------+--------+----------------+------------+-----------+-----------------+------------+-------+----------+
| A0003 | 1 | 0 | MDX | 2001 | 35000 | 1 | 2 | 90000 | (519) 541-1905 | | | 1728 Smith Lane | N7S3S8 | Blue | 1 |
| A0001 | 1 | 0 | MDX | 2000 | 14500 | 1 | 2 | 60000 | (519) 541-1905 | | | 1728 Smith Lane | N7S3S8 | Blue | 1 |
+-------+---------+------+-------+------+-------+--------------+-------+--------+----------------+------------+-----------+-----------------+------------+-------+----------+

Hello Unquick, You have a space in the first postal code and no space in the second postal code.

They will not match like this. i.e. N0M 2L0 not equal to N0M2L0

So think about adding a space or removing a space from the postal code data.

You might use left and right string in testing to see if this is the problem.

Select * from ad_listing where ad_listing.postalcode=(left(postalcode.postalcode,3) + right(postalcode.postalcode,3))

I have not tested the above sql code see http://mysql.com or search google for left and right string syntax.

thanks cornball

And it works. Well I feel silly... I thought I fixed that

Another one for the "live and learn" section!

Thanks for your help. It probably would have cost me a couple more hours, before I cought that! I was begining to think that MySQL no longer supported joins .. LOL

Cheers!

Hello Unquick, I have found breaking down queries in testing
to help my simple brain follow the logic and test if results are what they are
supposed to be a time saver as what I want is not always what I write
code [ sql queries ] to actually do.

It is almost as important as a " donut break " when things don't work.

After a coffee and donut you look again and usually see the missing link or logic.

thanks cornball

Hello Unquick, The images showing the tables are neat.

What software are you using that does this ?

Thanks cornball

I used a a trial version of Navicat
http://www.navicat.com

It's a great program for converting DBs or flatfiles ... but it has it's issues.
Such as overloading query results

Hello Unquick, I remember that one now.

I use SQLyog from http://www.webyog.com/index.php .

I like it way better than navicat.

Thanks cornball