CODEWITHSUNDEEP
sql
Nick LaMarca
Nick LaMarca

Reputation: 8188

Joining Based On An Aggregate Function

I have 2 tables that look like the following

Table 1

Name      Status          Code
----------------------------------------
pop       AB              CO-Completed
pop       AG              FF-Open

Table 2 

ID     Code
--------------
1       CO
2       FF

I am attempting to write a query that returns the status from table 1 and id from table given a code from table 2

I attempted to do this but I am looking at something wrong....

select * from Table1 a
join Table2 b
on a.SUBSTRING('Code',1,2)=b.Code
where b.ID = '1'

I want the return set to be

ID      Code     Status
-------------------------
1       CO       AB

Upvotes: 0

Views: 36

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You have single quotes around CODE, so you have a string constant. Remove those and the code will work. This method would more commonly be written with the left() function:

select *
from Table1 a join
     Table2 b
     on left(a.code, 2) = b.Code
where b.ID = 1;

Only use single quotes for string and date constants. Although they can be used in other contexts (depending on the database), they cause problems as you have just discovered.

Upvotes: 1

Related Questions