complicated JOIN construction - SQL Server Forums

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

Other Forums

MS Access

complicated JOIN construction

Author

Topic

Nosediver
Starting Member

7 Posts

Posted - 2007-10-10 : 02:32:34

Hi,
I am running into another non standard problem:
I have 2 tables, that have to be linked with a certain code. The problem is that in Table A it looks like this:
XX4536 while XX is fixed and the numbers are variable
XX6435
XX1234
...

Table B looks like this:
XX45
XX5326
XX453687
XX3456
...

Now I need a function to link:
XX4536 with XX453687 -> solved by restricting the field size of Table B to 6 characters

XX4536 with XX45 -> Here I have no idea how to do it. What I need is to match all Fields from Table A with all fields from Table B that are 4 characters long and begin with e.g. 45 in this case.

XX3425 with XX3425 -> is no problem

This is my current code: (I avoided the join function while I was trying around

SELECT A.[4DigitGCC], A.SupplierCapabilitySEA, A.[GLOBAL COMMODITY CODE DESCRIPTION (ENGLISH)], B.Description, B.Rating, B.Criteria, B.GSC, B.GCC
FROM 4x4 AS A, ASDBComplete AS B
WHERE (((A.[4DigitGCC]) = [B].[GCC]));

I would need something like 'includes' instead of the '='
I tried the 'LIKE' function but it didn't work.

Hope you have an idea,

Lars

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-10 : 03:11:24

[code]-- Prepare sample data
DECLARE @TableA TABLE (Code VARCHAR(10))

INSERT @TableA
SELECT 'XX4536' UNION ALL
SELECT 'XX6435' UNION ALL
SELECT 'XX1234'

DECLARE @TableB TABLE (Code VARCHAR(10))

INSERT @TableB
SELECT 'XX45' UNION ALL
SELECT 'XX5326' UNION ALL
SELECT 'XX453687' UNION ALL
SELECT 'XX3456'

-- Peso 1
SELECT *
FROM @TableA AS a
LEFT JOIN @TableB AS b ON b.Code LIKE a.Code + '%'

-- Peso 2
SELECT *
FROM @TableA AS a
LEFT JOIN @TableB AS b ON LEFT(b.Code, LEN(a.Code)) = a.Code[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources