Write query

Davin Martyn

11y926

Hi,
i have two table agentlist and agent . in agentlist table first_name, last_name, agency coloumn

and agent table name, cont_no. i want to find the agency column value
when we concat first_name+ ''+ last_name now compare with name column after concat name is avilable on name column then agency column related record show if not find then agency column show null.

how to write query please describe .
EX : NAME = RAJESH TRIPATHI

FIRST_NAME + ''+LAST_NAME = RAJESH TRIPATHI
NOW COMAPRE WITH NAME COLOUMN IF RAJESH TRIPATHI AVILABLE THEN AGENCY COLUMN SHOW RELATED RECORD.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sanjeeb Lenka11y ago

Select (al.FIRST_NAME +" ''+al.LAST_NAME) as Name , al.agency
From agentlist al inner join agent a
on a.name=(al.FIRST_NAME +" ''+al.LAST_NAME)
Abrar Ahmad Ansari11y ago
HI.. i have created both table as you have given requirement after that i have written the query.if it's solve your problem mark the answer.

CREATE TABLE Agent
(
AgentId int identity(1,1) ,
Name varchar(40)
)
Insert into Agent VALUES('Abrar');
Insert into Agent VALUES('Rajesh ');
Insert into Agent VALUES('Pavan K');
Insert into Agent VALUES('A Ansari');
Insert into Agent VALUES('Eshrar Ahmad');

CREATE TABLE AgentList
(
AgentListId int identity(1,1) ,
FName varchar(20),
LName varchar(20),
AgentName varchar(50)
)

Insert into AgentList VALUES('Abrar','Ahmad','Agency 1');
Insert into AgentList VALUES('Rajesh','Kumar','Agency 2');
Insert into AgentList VALUES('Pavan','K','Agency 3');
Insert into AgentList VALUES('A','Ansari','Agency 4');
Insert into AgentList VALUES('Eshar','Ahmad','Agency 5');

select Name,l.AgentName from Agent a
LEFT JOIN AgentList l on replace(a.Name,' ','')=REPLACE(l.FName+l.LName,' ','')