Stored Proc Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Stored Proc Question

Author

Topic

label
Posting Yak Master

197 Posts

Posted - 2003-02-14 : 08:42:39

I've written the following stored proc:

---------------------------------------------------------
SELECT sc.scid, sc.scname, st.StateLong FROM
smc_new_products.dbo.SupportCenters sc
left join
smc_new_products.dbo.supportcenterstates scs
on sc.scid=scs.scid
left join
smc_new_products.dbo.States st
on scs.stateid=st.stateid
WHERE Branch='1' ORDER BY SCName
---------------------------------------------------------

What this does is give me a list of all the Branchs I have (scname) and then all the States (statelong) associated with each Branch.

The problem: A single branch may have many states associated with it so when I get the results, The branch repeats itself for every state associated with it.

I understand that's what it's supposed to do given what I've currently written. However, I'd like to return results so that the Branch only shows up once.

Is there any way of doing this with SQL or am I stuck doing this on the programmatic side?

Liquid
Starting Member

4 Posts

Posted - 2003-02-14 : 09:07:30

Try DISTINCT sc.scname

label
Posting Yak Master

197 Posts

Posted - 2003-02-14 : 10:09:05

quote:

Try DISTINCT sc.scname

The results set doesn't return multiple branches because there are multiple branches in the table, it's returning multiple branches because I'm joining with another table that has a many to one relationship from States to Branches. So every state I get that mactches up with a branch, it' fills in the branch to establish the one to one relationship it needs to display.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-02-14 : 10:26:22

SELECT sc.scid, sc.scname FROM
smc_new_products.dbo.SupportCenters sc
left join
smc_new_products.dbo.supportcenterstates scs
on sc.scid=scs.scid
left join
smc_new_products.dbo.States st
on scs.stateid=st.stateid
WHERE Branch='1' ORDER BY SCName
GROUP BY sc.scid, sc.scname

label
Posting Yak Master

197 Posts

Posted - 2003-02-14 : 10:34:59

quote:

SELECT sc.scid, sc.scname FROM
smc_new_products.dbo.SupportCenters sc
left join
smc_new_products.dbo.supportcenterstates scs
on sc.scid=scs.scid
left join
smc_new_products.dbo.States st
on scs.stateid=st.stateid
WHERE Branch='1' ORDER BY SCName
GROUP BY sc.scid, sc.scname

This doesn't return the state name....

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-02-14 : 19:45:18

You'll have to be more specific and tell us which state you want if there are multiple states. MIN, MAX, ORDER IN ANOTHER TABLE ???????

or provide an example of how you want the data returned.

Edited by - ValterBorges on 02/14/2003 19:52:07

label
Posting Yak Master

197 Posts

Posted - 2003-02-17 : 10:39:45

quote:

You'll have to be more specific and tell us which state you want if there are multiple states. MIN, MAX, ORDER IN ANOTHER TABLE ???????

or provide an example of how you want the data returned.

I want all the states associated with a single branch to return. For example, in a datagrid I would want to display the following results:

Branch 1 - Indiana
Ohio
Kentucky

Branch 2 - New York
New Mexico
New Jersey

Brach 3 - Utah

Branch 4 - Colorado
- California

etc, etc......

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-02-17 : 11:07:32

WAIT, stop, stop, stop.

The data you are returning is 100% correct, as is your query.

From your first post:

quote:

The problem: A single branch may have many states associated with it so when I get the results, The branch repeats itself for every state associated with it.

I understand that's what it's supposed to do given what I've currently written. However, I'd like to return results so that the Branch only shows up once.

This is a formatting/presentation issue -- do NOT try to use the Query Analyzer as a report writer! Use Access, Crystal reports, or anything along those lines.

Then you just set up a group header for Branch and in the detail of the report you list all of the states associated with it. The group header only prints 1 time for each branch.

Or, you put it all in the detail section and set the Branch field to be "non-repeating" so it prints only once per branch, and be sure to sort the final output by Branch so you get the desired effect.

Do not focus on formatting the output of your T-SQL in the query analyzer!!! It is not designed for that purpose. Trust me, invest in MS Access and you will be printing beautiful reports with graphics and charts and multiple columns and subreports and you'll be loving life, don't try to force formatting output from the simple text grid in the query analzyer.

Focus on getting the DATA you need from SQL Server, and use another app for formatting or presenting the data.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources