mysql - cross table query

[NickPR]

I am trying to display an associated table field but am not having much luck with any method i try 🙁

I have 2 tables called: sites and regions.
I am performing a seach of the sites table where the postcode is like "nn" for e.g. Along with the result set from the sites table, i'm trying to display the 'town' field belonging to the regions table.

SELECT sites., regions.
FROM sites AS sites
LEFT JOIN regions AS regions ON sites.zipcode = regions.zipcodes
WHERE sites.zipcode like \"%$trimmed%\" ORDER BY sitez.id, town ASC

The association is with the postcode. In the sites table, there is a field called postcode and in the regions table, there is a postcodes field. Along side all of the regions.postcodes are towns, city, regions etc...

Is there a clever way to do this? or do i need place a town, city etc. field inside of my sites table? (making regions void and useless).

the latest attempt i have looks like this:

Heres what i have atm:

mysql> SELECT * FROM regions RIGHT JOIN sites ON sites.postcode = regions.postcodes;
+------+------+--------+-----------+------+---------+----+-----------------------+--------------------------------+----------+---------+
| id | town | county | postcodes | area | country | id | name | description | postcode | produce |
+------+------+--------+-----------+------+---------+----+-----------------------+--------------------------------+----------+---------+
| NULL | NULL | NULL | NULL | NULL | NULL | 1 | Woodburn House | This is nothing but its a test | NN6 6NB | 4678 |
| NULL | NULL | NULL | NULL | NULL | NULL | 2 | Smelly Cheese Cottage | blah blah blah | NG3 3PG | 4689 |
+------+------+--------+-----------+------+---------+----+-----------------------+--------------------------------+----------+---------+
2 rows in set (0.01 sec)

Could it be that my regions.postcodes fields contain more than the 1 postcode? i.e: The contents of one is:

B1, B2, B3, B4, B5, B6, B7, B8, B9, B10, B11, B12, B13, B14, B15, B16, B17, B18, B19, B20, B21, B22, B23, B24, B25, B26, B27

Many thanks in advance 🙂

[tomhath]

In both examples you posted you are joining on sites.zipcode = regions.zipcodes (or is it sites.postcode = regions.postcodes ?, whatever...)

So the two fields have to match exactly. If regions.postcodes contains a list and sites.zipcode only contains a single value you'll have to change the join to be something like this:

... ON regions.postcodes like '%' + sites.postcode + '%'

[NickPR]

Hi Tom, thanks for the reply.

I tried your sugestion and the output doesn't seem to alter:

mysql> SELECT * FROM regions RIGHT JOIN sites ON regions.postcodes like '% +sites.postcode+ %';
+------+------+--------+-----------+------+---------+----+-----------------------+--------------------------------+----------+---------+
| id | town | county | postcodes | area | country | id | name | description | postcode | produce |
+------+------+--------+-----------+------+---------+----+-----------------------+--------------------------------+----------+---------+
| NULL | NULL | NULL | NULL | NULL | NULL | 1 | Woodburn House | This is nothing but its a test | NN6 | 4678 |
| NULL | NULL | NULL | NULL | NULL | NULL | 2 | Smelly Cheese Cottage | blah blah blah | NG3 3PG | 4689 |
+------+------+--------+-----------+------+---------+----+-----------------------+--------------------------------+----------+---------+
2 rows in set (0.00 sec)

It took me a while to get the wildcards to work though, so maybe i'm still not using them correctly.

[tomhath]

Are there any records in regions that have postcodes 'NN6' or 'NG6 3PG' in them? It might just be your data.

[NickPR]

yeah, NG6 should come up as the following data exists:

Nottingham Nottinghamshire NG1, NG2, NG3, NG4, NG5, NG6, NG7, NG8, NG9, NG10, NG11, NG12, NG13, NG14, NG15, NG16 Nottingham England