SelfJoin query

[D]

Hi:

Can you please tell me how can I modify this query to identify these 'pairs':

1234A and 1234B; the below query is working when I have 1234A and 1234AX;
the characters before X; or in the first case before A and B are the same.

Thank you,

Dan

****

SELECT A1.DEALNO, B1.DEALNO
FROM test AS A1 INNER JOIN test AS B1 ON B1.DEALNO = A1.DEALNO & "B";

 

[D]

Hi Marshall:

Thank you! It gives me everything; I need just sameA and sameB....

Here is SQL:
***
SELECT A.DEALNO, B.DEALNO
FROM test AS A INNER JOIN test AS B ON Left(A.DEALNO, Len(A.DEALNO) - 1)
= Left(B.DEALNO, Len(B.DEALNO) - 1)
Or Left(A.DEALNO, Len(A.DEALNO) - 1) = B.DEALNO
Or Left(B.DEALNO, Len(B.DEALNO) - 1) = A.DEALNO
***


If any other idea please let me know

Thanks again,

Dan

 

[D]

Hi Marshall:

I think I am getting there with another SQL based on your:
SELECT DISTINCT [qry-pairs-new].A.DEALNO, [qry-pairs-new].B.DEALNO
FROM [qry-pairs-new]
WHERE ((([qry-pairs-new].A.DEALNO) Like "*A") AND
(([qry-pairs-new].B.DEALNO) Like "*B"));



Thanks a lot,

Dan

 

[Marshall Barton]

Can you please tell me how can I modify this query to identify these 'pairs':

1234A and 1234B; the below query is working when I have 1234A and 1234AX;
the characters before X; or in the first case before A and B are the same.

SELECT A1.DEALNO, B1.DEALNO
FROM test AS A1 INNER JOIN test AS B1 ON B1.DEALNO = A1.DEALNO & "B";

Does this do what you want

ON Left(A1.DEALNO, Len(A1.DEALNO) - 1)
= Left(B1.DEALNO, Len(B1.DEALNO) - 1)
Or Left(A1.DEALNO, Len(A1.DEALNO) - 1) = B1.DEALNO
Or Left(B1.DEALNO, Len(B1.DEALNO) - 1) = A1.DEALNO

You will not be able to do that in the query design grid so
stay in SQL view.

 

[Marshall Barton]

Thank you! It gives me everything; I need just sameA and sameB....

SELECT A.DEALNO, B.DEALNO
FROM test AS A INNER JOIN test AS B ON Left(A.DEALNO, Len(A.DEALNO) - 1)
= Left(B.DEALNO, Len(B.DEALNO) - 1)
Or Left(A.DEALNO, Len(A.DEALNO) - 1) = B.DEALNO
Or Left(B.DEALNO, Len(B.DEALNO) - 1) = A.DEALNO

Or A.DEALNO = B.DEALNO