| Ms Access LIKE Sub Select SQL [message #372946] |
Mon, 19 March 2001 17:01  |
Mark Holland
Messages: 1
Registered: March 2001
|
Junior Member |
|
|
Right. I'm really confused on this one. I'm trying to merge postcodes in one table with the geographical information on a another.
The tables are as follows:
TABLE1(tbl_postcode_data)
pcode, xcoords, ycoords
TABLE2(tbl_restaurants)
pcode
I've basically got this:
SELECT * FROM tbl_restaurants
WHERE EXISTS
(SELECT pcode, xcoords, ycoors from tbl_postcode_data
WHERE tbl_restaurants.postcode LIKE "tbl_postcode_data.pcode*");
Basically the column in tbl_restaurants would be say "SW15 8UY" and would match a column "SW15 8" in tbl_postcode_data. Only problem is access doesn't like the way i've quoted the field or something... I've tried it with all sorts of ' ' quotes and "' '" and none work. Any ideas??
Cheers,
Mark
|
|
|
|
| Re: Ms Access LIKE Sub Select SQL [message #372949 is a reply to message #372946] |
Mon, 19 March 2001 17:38  |
Joachim Lindner
Messages: 30
Registered: February 2001
|
Member |
|
|
Wrong board, I'd say ... :-)
I have no experience with Access-SQL but maybe your first problem is
not the LIKE syntax but the "WHERE tbl_restaurants.postcode" which
should read "WHERE tbl_restaurants.pcode" according to the table
structure you presented.
Why, at all, do you "SELECT pcode, xcoords, ycoors from tbl_postcode_data ..."
for an EXISTS subquery ? A " Select 1 ..." would suffice and be "cheaper", at
least in Oracle SQL.
Besides that, your second problem is that your query will not merge anything
when it executes. For merging purposes I would suggest a join construct, in case Access supports table joins :-)
Cheers, Joachim
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
