Need to SELECT Columns in Table A where one column closely matches in Table B

Question

Need to SELECT Columns in Table A where one column closely matches in Table B

tim8w

Old Hand

Points: 375
More actions
March 17, 2021 at 8:52 pm

#3859146
I have the following two tables:
Table A:
WONum OpSeqNum CellName
1547434 1.00 1101ENG
1547434 10.00 1101STKRM
1547434 20.00 1101PREC

Table B:
WCName
STKRM
LASER
PREC

I tried using the following SELECT and it works sometimes and doesn't others. In particular it doesn't seem to find a match when A.OpSeqNum = 1101PREC. Is there a better way to go about this?
```
SELECT        A.OpSeqNum, A.CellName
FROM            A CROSS JOIN
                         B
WHERE        (A.WONum = 1547434) AND (A.CellName LIKE '%' + B.WorkCenterName + '%')
ORDER BY A.OpSeqNum
```
- This topic was modified 3 years, 8 months ago by tim8w. Reason: typo
- This topic was modified 3 years, 8 months ago by tim8w. Reason: typo
- This topic was modified 3 years, 8 months ago by tim8w. Reason: typo

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ratbak Ten Centuries Points: 1358 More actions · Answer 1

It would help if you provided DDL and insert statements for data... and if your query column names matched those you provided in the table descriptions -- WorkCenterName != WCName

Given the discrepancy, you should get an error, not results.

But the following does return 1101PREC:

DROP TABLE IF EXISTS #TABLE_A
CREATE TABLE #TABLE_A
    (
    WONum   INT          NOT NULL,
    OpSeqNum DECIMAL(5,2) NOT NULL,
    CellName  VARCHAR(16)  NOT NULL,
    PRIMARY KEY (WONum, OpSeqNum)
    );

DROP TABLE IF EXISTS #TABLE_B
CREATE TABLE #TABLE_B
    (
    WCName VARCHAR(5) NOT NULL PRIMARY KEY
    );

INSERT INTO #TABLE_A 
       (WONum,  OpSeqNum,CellName)
VALUES (1547434, 1.00,'1101ENG'),
       (1547434,10.00,'1101STKRM'),
       (1547434,20.00,'1101PREC');

 
INSERT INTO #Table_B (WCName)
VALUES ('ENG'),
       ('STKRM'),
       ('LASER'),
       ('PREC');

SELECT 
    #Table_A.OpSeqNum, 
    #Table_A.CellName
FROM  #Table_A
CROSS JOIN #Table_B
WHERE
    #Table_A.WONum = 1547434 AND 
    #Table_A.CellName LIKE '%' + #Table_B.WCName + '%'
ORDER BY 
    #Table_A.OpSeqNum;

ScottPletcher SSC Guru Points: 101097 More actions · Answer 2

You're likely wasting resources by using a CROSS JOIN. Just do a standard INNER JOIN using the LIKE comparison as the join condition.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 3

ScottPletcher wrote:

You're likely wasting resources by using a CROSS JOIN. Just do a standard INNER JOIN using the LIKE comparison as the join condition.

The optimiser should make them have the same execution plan.

tim8w Old Hand Points: 375 More actions · Answer 4

Same SELECT that I am using. Ending up being that the Record that contained 'PREC' in Table B somehow had some non-visible character in it. I deleted the value and re-entered it and everything worked fine.