2 Replies - 1565 Views - Last Post: 08 December 2008 - 02:11 PM Rate Topic: -----

#1 haynest  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 43
  • Joined: 09-November 08

SQL Server 2005 simple query question. I almost have it.

Post icon  Posted 07 December 2008 - 06:32 PM

Hello everybody,

I am trying to create a stored procedure to accomplish 2 things. I've commented my code below to help you understand my logic.

/* @Promo='somechars' */

SELECT @Aid=AgentId  /*assign the selected column value to @Aid */
FROM ATABLE /*from this table */
WHERE PromoNumber=@Promo  /*where col PromoNumber='somechars'

SELECT AName As AgentName,
            Office,
            Email
FROM ANOTHERTABLE WHERE Id=@Aid /*Get all info for AgentID from first select*/

/*Then start from the first SELECT again to display the next AgentID
     that the WHERE specifies*/


However, my code will only ouput a single AgentId. There should be 4 AgentId's that meet the conditions of the WHERE clause in the first select statement.

Any Ideas? Thanks! I hope my explanation was good enough. I'm here so reply if you have any questions.

Is This A Good Question/Topic? 0
  • +

Replies To: SQL Server 2005 simple query question. I almost have it.

#2 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1524
  • View blog
  • Posts: 5,960
  • Joined: 21-March 08

Re: SQL Server 2005 simple query question. I almost have it.

Posted 08 December 2008 - 12:07 PM

SELECT 
     AnotherTable.AName AS 'AgentName'
     , AnotherTable.Office
     , AnotherTable.Email
FROM AnotherTable JOIN ATable 
          ON AnotherTable.Id = ATable.AgentID
WHERE ATable.PromoNumber = @Promo

Was This Post Helpful? 0
  • +
  • -

#3 xerxes333  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 31
  • View blog
  • Posts: 504
  • Joined: 05-July 07

Re: SQL Server 2005 simple query question. I almost have it.

Posted 08 December 2008 - 02:11 PM

The reason you are getting only one result back is due to your first SQL statement.
SELECT @Aid=AgentId  /*assign the selected column value to @Aid */
FROM ATABLE /*from this table */
WHERE PromoNumber=@Promo  /*where col PromoNumber='somechars'

The above SQL assigns the value of AgentId to the variable @Aid for each record that matches @Promo and each time it finds a match @Aid gets updated with the new AgentId value. So if you run a simple select statement like
SELECT @AgentId FROM ATABLE WHERE PromoNumber=@Promo

your results would look something like

-- AgentId ---
111
222
333

but because you are assigning the table result to a variable (@Aid) each time a new record is found @Aid gets the new value. Hence only one agent is being "found".

eclipsed4utoo has provided you with a solution to your problem. I just felt it was important for you to know WHY you were getting the results you were.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1