Write query

Question

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.

Sanjeeb Lenka · Answer

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 Ansari · Answer

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(11)  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(11)  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 Namel.AgentName from Agent a LEFT JOIN AgentList l on replace(a.Name' ''')=REPLACE(l.FName+l.LName' ''')