use LIKE in an INNER JOIN statement in Access

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

use LIKE in an INNER JOIN statement in Access

Author

Topic

jarv
Posting Yak Master

131 Posts

Posted - 2007-07-21 : 10:10:04

ok so now I have:

select pubs.rsTown, count(*) from pubs INNER join members on pubs.rsPostCode=members.rsPostCode group by pubs.rsTown

the above statement works but only selects the exact same postcodes, i would only like to select the postcode LIKE the postcode in the members table
just the first 4 characters of the postcode match?!

or couldn't i use the left,4 function somewhere?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-21 : 10:12:35

Have you tried it yet?

select pubs.rsTown, count(*) from pubs INNER join members on pubs.rsPostCode LIKE members.rsPostCode group by pubs.rsTown

Peter Larsson
Helsingborg, Sweden

jarv
Posting Yak Master

131 Posts

Posted - 2007-07-21 : 10:16:48

i have yes, it didn't work?!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-21 : 10:23:10

select pubs.rsTown, count(*) from pubs INNER join members on replace(pubs.rsPostCode, ' ', '') LIKE members.rsPostCode + '%' group by pubs.rsTown

Peter Larsson
Helsingborg, Sweden

jarv
Posting Yak Master

131 Posts

Posted - 2007-07-21 : 10:28:41

thanks, gave that a try, now i get:

Undefined function 'replace' in expression.
/pubspace/main.asp, line 11

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-21 : 10:30:43

REPLACE has another name in MS Access. Use the function name that substitutes characters.
Or, in the future, post in the proper MS Access forum.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-21 : 10:31:03

Also, wildcard character % is * in MS Access.

Peter Larsson
Helsingborg, Sweden

jarv
Posting Yak Master

131 Posts

Posted - 2007-07-21 : 10:37:26

filter?

jarv
Posting Yak Master

131 Posts

Posted - 2007-07-21 : 10:51:34

I have changed my SQL statement as i want to count the members not the towns!

i have:

select members.UserID, count(*) from members INNER join members on pubs.left(rsPostCode,4)=members.rsPostCode group by pubs.rsTown

and now i get the error: Syntax error in JOIN operation.
/pubspace/main.asp, line 11

jarv
Posting Yak Master

131 Posts

Posted - 2007-07-21 : 14:58:01

okso i have reverted back to the original SQL:

SELECT Count(m.*) AS MembersCount FROM pubs p INNER JOIN members m ON p.rsPostcode LIKE m.rsPostcode

now i get the error:
Syntax error in query expression 'Count(m.*)'

jarv
Posting Yak Master

131 Posts

Posted - 2007-07-21 : 15:10:51

I have changed it round a bit like this now:


SELECT Count(m.*) AS MembersCount FROM members m INNER JOIN pubs p ON m.rsPostcode LIKE p.rsPostcode

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources