Using LIKE in a JOIN

Using LIKE in a JOIN

Post by Jason Lepa » Thu, 25 Jan 2007 02:07:34


The question is what would happen if there was:

REPORT_NUM various columns...
RT00029
RTP00029
RTP00129
RT00129

Query 1
RPT CREATOR
29 Customer

what would the expected output be in this situation?
 
 
 

Using LIKE in a JOIN

Post by Jason Lepa » Thu, 25 Jan 2007 03:01:02

o the report_num items always have some letters and then a 5 digit
number?

Cheers,
Jason Lepack

Jay wrote:


 
 
 

Using LIKE in a JOIN

Post by John Vinso » Thu, 25 Jan 2007 03:04:21

On Tue, 23 Jan 2007 09:01:00 -0800, Jay



So it can be ANYWHERE!?

What would you do with a REPORT_NUM of

RPT034730

That contains 34, 47, 73, and 30 - which is the desired substring?

Garbage in, garbage out, I fear!!

Your *examples* all have the creator code as the rightmost two
characters: can you at least count on that? If so,

SELECT <whatever fields from Table1>, Table2.Creator
FROM Table1 INNER JOIN Table2
ON Table2.RPT = Right(Table1.Report_Num, 2);

But if you can't ascertain WHICH two digits in the field provide the
link I think you're out of luck.

John W. Vinson[MVP]
 
 
 

Using LIKE in a JOIN

Post by SmF5 » Thu, 25 Jan 2007 03:14:03

forgot to mention, I'm using Access 97. That might limit my options.

"Jason Lepack" wrote:

 
 
 

Using LIKE in a JOIN

Post by Jason Lepa » Thu, 25 Jan 2007 04:42:21

I wrote this function, paste it into a module and save the module to be
able to use it:
Public Function getNumber(s As String) As Long
Dim x As String
x = s
Do While Not IsNumeric(x)
x = Mid(x, 2)
Loop
getNumber = CLng(x)
End Function

I used this query: (Table 1 and Query 1 are from your definitions in
your original post)
SELECT A.*, B.Creator
FROM (SELECT *, getNumber([REPORT_NUM]) AS X FROM [Table 1]) AS A
INNER JOIN [Query 1] AS B
ON A.X = B.RPT

It may be a bit slow but it will work. See what you think.

Cheers,
Jason Lepack
 
 
 

Using LIKE in a JOIN

Post by SmF5 » Thu, 25 Jan 2007 05:04:01

Jason,

I'll have to try it out, thanks. Even if it doesn't work, you helped me
learn something. I wasn't aware you could call a function from a query. I
might play with that a bit more.

Currently, I edited my upload routine to perform the same actions as your
function and added another column to Table1.

Now it'll be stored in the DB. Not a perfect solution, but it seems to work.

Thanks again,
Jay
 
 
 

Using LIKE in a JOIN

Post by Jamie Coll » Fri, 26 Jan 2007 21:57:02


Here's a more direct answer to the question, "Using LIKE in a JOIN".

I note you are using Access 97 and the following is ANSI-92 Query Mode
syntax. However, the same principle should be possible in Access 97 if
you substitute the ANSI-89 Query Mode wildcard character * in place of
%; also, I'm not sure if Jet 3.n supports derived tables, in which case
you may need to persist the SQL definition of the derived table (DT1)
in a Query object:

CREATE TABLE Table1 (
REPORT_NUM VARCHAR(8) NOT NULL UNIQUE
)
;
INSERT INTO Table1 (REPORT_NUM)
VALUES ('RPT00011')
;
INSERT INTO Table1 (REPORT_NUM)
SELECT TEMP.REPORT_NUM FROM
(
SELECT 'RT00029' AS REPORT_NUM
FROM Table1
UNION ALL
SELECT 'RTP00030'
FROM Table1
UNION ALL
SELECT 'REP00034'
FROM Table1
UNION ALL
SELECT 'RPT00047'
FROM Table1
) AS TEMP
;
CREATE TABLE Query1 (
RPT CHAR(2) NOT NULL UNIQUE,
CREATOR VARCHAR(12) NOT NULL
)
;
INSERT INTO Query1 (RPT, CREATOR)
VALUES ('11', 'Vendor')
;
INSERT INTO Query1 (RPT, CREATOR)
SELECT TEMP.RPT, TEMP.CREATOR
FROM (
SELECT '29' AS RPT, 'Customer' AS CREATOR
FROM Query1
UNION ALL
SELECT '30', 'Employee'
FROM Query1
UNION ALL
SELECT '47', 'Vendor'
FROM Query1
) AS TEMP
;

Proposed ANSI-92 solution:

SELECT T1.REPORT_NUM, DT1.CREATOR
FROM Table1 AS T1
LEFT JOIN
(
SELECT T2.REPORT_NUM, Q1.RPT, Q1.CREATOR
FROM Table1 AS T2, Query1 AS Q1
WHERE T2.REPORT_NUM LIKE ('%' & Q1.RPT)
) AS DT1
ON T1.REPORT_NUM = DT1.REPORT_NUM
;

Alternative proposed ANSI-89 solution (untested): with the following
persisted a Query object named View1:

SELECT T2.REPORT_NUM, Q1.RPT, Q1.CREATOR
FROM Table1 AS T2, Query1 AS Q1
WHERE T2.REPORT_NUM LIKE ('*' & Q1.RPT)
;

use View1 in a separate query:

SELECT T1.REPORT_NUM, DT1.CREATOR
FROM Table1 AS T1
LEFT JOIN View1 AS V1
ON T1.REPORT_NUM = V1.REPORT_NUM
;

Jamie.
 
 
 

Using LIKE in a JOIN

Post by Jamie Coll » Fri, 26 Jan 2007 22:27:27


Oops! Typo in that last query. Should be:

SELECT T1.REPORT_NUM, V1.CREATOR
FROM Table1 AS T1
LEFT JOIN View1 AS V1
ON T1.REPORT_NUM = V1.REPORT_NUM
;

Jamie.