CODEWITHSUNDEEP
sqlt-sql
NoteStack
NoteStack

Reputation: 13

vlookup style query in sql?

I have a really simple vlookup query I want to write in sql but cant seem to apply any of the examples from previous questions to my data.

I have two tables, totalstats which has the agentnumber column, and Agentref which lists every agentname against the agentnumber.

I want to do a really simple query to join the two tables and create a new column in totalstats which shows the agent name against each agentnumber.

I feel like this should be simple to do but i'm getting really stuck! Would appreciate any help, thanks :)

Upvotes: 1

Views: 203

Answers (2)

Der U
Der U

Reputation: 3314

select
   Agentref.AGENTNUMBER
   ,totalstats.stat
FROM
   totalstats 
JOIN
   Agentref
ON 
   totalstats.AGENTNUMBER=Agentref.AGENTNUMBER

UPDATE:

assuming the Agentname is in a column like "AGENTNAME"

select
   Agentref.AGENTNUMBER
   ,Agentref.AGENTNAME
   ,convert(varchar(255),Agentref.AGENTNUMBER)+' '+Agentref.AGENTNAME as NumberName
   ,totalstats.stat
FROM
   totalstats 
JOIN
   Agentref
ON 
   totalstats.AGENTNUMBER=Agentref.AGENTNUMBER

convert(varchar(255),Agentref.AGENTNUMBER)+' '+Agentref.AGENTNAME
puts Number and Name in the same column with a blank inbetween

Upvotes: 2

Jon Bellamy
Jon Bellamy

Reputation: 3373

Try...

SELECT
*
FROM
dbo.TotalStats TS
LEFT JOIN
dbo.AgentRef AR ON TS.AgentNumber = AR.AgentNumber

That is, assuming you've structured the data as I think you have.

Upvotes: 1

Related Questions