Using a wildcard on a column

[RFH Reseller: Rappie]

Hi Guys,

I want to use the % wildcard on a query to match some phrases from one table to a column on another table . But I am having doubts whether I am actually doing the right thing here.

This my query:
SELECT name, phonenumber, address, streetname FROM `wb_stores`, `wb_store-street` WHERE wb_store-street.streetname LIKE wb_stores.%address%

There are two tables:
wb_stores

+--------+-------------+--------+--------------------+
| ID     |   shopname  |  tel   |       address      |
+--------+-------------+--------+--------------------+
| 10000  | Tesco       | 205587 |  main street 55a   |
| 10001  | Halfords    | 208790 |  north Boulevard 4 |
| 10002  | T-Mobile    | 204221 |  north Boulevard 8 |
| 10003  | H & M       | 215889 |  main street 43c   |
| 10004  | McDonald's  | 205952 |  main street 43b   |
| 10005  | Jack & Jones| 207285 |  kingfisher road 2 |
| 10006  | Diesel      | 202085 |  Hayne road 22f    |
+--------+-------------+--------+--------------------+

wb_store-street

+--------------------+
|     streetname     |
+--------------------+
|  main street       |
|  kingfisher road   |
|  north Boulevard   |
|  Hayne road        |
|  main street       |
|  Conway street     |
|  Borough road      |
|  Price street      |
|  Holland road      |
|  Hamilton square   |
+--------------------+

I need to match the street name (from table: wb_store-street)with the street name part in de address (from table: wb_stores). Any clue's ?

[Staff: Warren Ashcroft]

Are there any identifiers in the wb_store-street table? Or rather what are you trying to gain from that table?

[RFH Reseller: Rappie]

The wb_store-street table doesn't have any other columns. The result I am after is a list of all shops ordered and categorized by street name. For example:

- main street
Tesco
H & M
McDonald's

- north Boulevard
Halfords
T-Mobile

[Staff: Warren Ashcroft]

Off the top of my head I can only think of using a sub query which may not be the best/most efficient way:

SELECT *, (SELECT streetname FROM wb_store-street WHERE wb_stores.address LIKE '%' + wb_store-street.streetname + '%') AS streetname FROM wb_stores

[RFH Reseller: SoharaDesign]

It would be much easier to split the street and number into seperate columns:

wb_stores

+--------+-------------+--------+--------------------+----------------+
| ID     |   shopname  |  tel   |       street       |     number     |
+--------+-------------+--------+--------------------+----------------+
| 10000  | Tesco       | 205587 |  main street       |  55a           |
| 10001  | Halfords    | 208790 |  north Boulevard   |  4             |
| 10002  | T-Mobile    | 204221 |  north Boulevard   |  8             |
| 10003  | H & M       | 215889 |  main street       |  43c           |
| 10004  | McDonald's  | 205952 |  main street       |  43b           |
| 10005  | Jack & Jones| 207285 |  kingfisher road   |  2             |
| 10006  | Diesel      | 202085 |  Hayne road        |  22f           |
+--------+-------------+--------+--------------------+----------------+

Then, with your streets table...

SELECT *
FROM wb_stores
INNER JOIN wb_stores-street
    ON wb_stores.street = wb_stores-street.streetname
WHERE wb_stores-street.streetname = [VARIABLEGOESHERE]

Now, I can't actually look at the word street without it looking totally wrong...

[RFH Reseller: Wise Webs]

You could try

SELECT name, phonenumber, address, streetname
FROM wb_stores
inner join wb_store-street on left(wb_stores.address ,len(wb_store-street.streetname )) = wb_store-street.streetname

In an ideal world you would have your wb_store-street table linked to you wb_store table by a recordid to avoid duplication of address streets.

However, this SQL matches the wb_store-street.streetname to the wb_stores.address, only matching up to the length of the wb_store-street.streetname, thereby ignoring the address street number.