Query Help (Should be Easy)

[Esoteric]

Here is what I am doing and for some reason I am brain dead today.

I have a query that is like this

DECLARE @CNUM AS INT
SELECT @CNUM = CUSTMNBR FROM CUSTOMERS

SELECT AGENTNAME, STATEN, COMRATE, AGCUSTNM FROM AGENTS WHERE AGCUSTNM = @CNUM

I am only getting a single result, Is there something I am doing wrong I should get like 879 results. I know this looks stupid but I have to do it this way. I want the Result set to return for each result from the first Query.

Result like this
FRED MI .25 02325425
FRED MI .22 98783634
JIMBO IL .18 23963666

etc.

 

[gmmastros]

The problem is that @CNUM is a scalar (It can only hold 1 value).

Instead, you should use a join to get the info you want, like this...

SELECT AGENTNAME, 
       STATEN, 
       COMRATE, 
       AGCUSTNM 
FROM   AGENTS 
       INNER JOIN CUSTOMERS
         ON AGENTS.AGCUSTNM = CUSTOMERS.CUSTMNBR

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Esoteric]

Problem is when I do this on my real query it takes too long to execute. Is there a way I can do almost like a recordset return then exectute for each record?

 

[SQLSister]

That is a very bad idea, it will take much much much longer to loop through the records one at a time than to do the setbased operation. On the order of hours longer. This is why we almost always recommend the person take cursors and loops out of their code. You should never consider running anything one record at a time in a SQl Server database.

as to why it is taking so long when done in a join, perhaps we could better help you if you gave us the complete query to work from and some information about expected results and the table structures.

Questions about posting. See faq183-874

 

[gmmastros]

If the query is slow, then you should probably look at your indexes. Don't know what indexes are, or what indexes are set on your tables? That's ok, here's what you should do...

Load the query in to Query Analyzer, then press CTRL-I. This will bring up the index tuning wizard. Run through the wizard and see what happens.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom