Need help with query/stored procedure, SQL 2005

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

General SQL Server Forums

New to SQL Server Programming

Need help with query/stored procedure, SQL 2005

Author

Topic

Joker444
Starting Member

4 Posts

Posted - 2009-01-28 : 19:59:50

Hello. I'm new to SQL but picking it up pretty quickly. I am stumped on a problem I came across.

Unfortunately my work is done on a secure work server so I don't have access to it right now.

The main jist is I have a series of tables my query will use. On one particular table, there is a column that contains multiple entries. I want to include in my query to check this column and return the value of the entry if there is only 1, or return the string "Multiple" if there is more than 1. I only want 1 return value.

My memory is not that great, but I believe using hypothetical tables it would be something like this. Let's say I have a spec's table that has a field named "doors". (Excuse my rudimentary coding. I'm not good at pulling this stuff out from memory)

string @doors

SELECT cars.ID, cars.Spec cars.Name, cars.Model, @doors
FROM cars
INNER JOIN specs ON cars.ID = spec.ID
(if specs.doors contains only 1 record then @doors = spec.doors else @doors = "Multiple")

I'm sorry if this type of question has been answered before. I've tried to search but wasn't able to find a solution. It might be because I'm not sure what search terms to use.

AvanthaSiriwardana
Yak Posting Veteran

78 Posts

Posted - 2009-01-28 : 21:58:59

Hi check this and let me know
string @doors

SELECT cars.ID, cars.Spec cars.Name, cars.Model, @doors
FROM cars
INNER JOIN specs ON cars.ID = spec.ID
WHERE (SELECT * FROM specs,
CASE
WHEN specs.doors = 1 THEN @doors = spec.doors
ELSE @doors = "Multiple
)
)

Avantha Siriwardana

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-28 : 23:50:56

quote:
Originally posted by AvanthaSiriwardana

Hi check this and let me know
string @doors

SELECT cars.ID, cars.Spec cars.Name, cars.Model, @doors
FROM cars
INNER JOIN specs ON cars.ID = spec.ID
WHERE (SELECT * FROM specs,
CASE
WHEN specs.doors = 1 THEN @doors = spec.doors
ELSE @doors = "Multiple
)
)

Avantha Siriwardana

This won't give you results.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-28 : 23:55:43

Maybe this one:

Select N.ID,N.specs,N.Carname,N.CarModel,(Case when DoorCount =1 then 
convert(varchar(10),DoorCount) else 'Multiple' end)as Doorcount
from
(Select p.ID,p.specs,p.Carname,p.carmodel,Count(s.doors)as DoorCount
from dbo.Cars p
inner join dbo.Specs s
on p.ID = s.ID
Group by p.ID,p.specs,p.Carname,p.carmodel)N

Joker444
Starting Member

4 Posts

Posted - 2009-01-29 : 08:32:34

I will give it a try today. I think I have enough good ideas here to see how the structure would go. It looks like I wasn't thinking about the "count" method to count the returned records. Doors was just an example, the actual table has strings in it so it's not as easy as just looking for a particular name like "1".

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-29 : 09:24:38

your question is not clear. are you trying to display the value or do you want to use it for filtering?

Joker444
Starting Member

4 Posts

Posted - 2009-01-29 : 12:33:11

Yes, I'm sorry. I've jotted down my actual code so I can show exactly what I need help with.

Here is my current precedure,

As
BEGIN
SELECT T.tvID, T.tvCatID, TC.tvCat,
TL.tvLocID, TL.City, T.Title,
FROM TV T INNER JOIN TVCat TC ON T.CatID = TC.tvCatID
INNER JOIN TVStationLocs TTL ON T.tvID = TTL.tvID
INNER JOIN TVLocs TL ON TTL.LocID = TL.LocID
ORDER BY Title
END
SET QUOTED_IDENTIFIER OFF

My tables are as such,
TV has key of tvID in it,
TVStationLocs has the tvID key and a LocID
TVLocs has a LocID and has a Cities in which I'm trying to get.

In my TVLocs table, there can be multiple cities per LocID. I don't want to return back a list of all the cities associated with a LocID. I only want one return value.

If a LocID has multiple cities associated with it, I want to return back the string value of "Multiple". If there is only one city associated with the LocID, than I want to return back that name of that city.

I played around with it again today with some of the above ideas, and couldn't get it to do what I need it to do. Hopefully this will make it more clear on what I'm looking for. Thanks for any assistance that can be provided. I'm thinking it might be a somewhat simple command, but I am very new and not yet familiar with all the SQL tricks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-29 : 12:39:19

try like below


As
BEGIN
SELECT T.tvID, T.tvCatID, TC.tvCat, 
CASE WHEN MIN(TL.tvLocID)= MAX(TL.tvLocID) THEN CAST(MIN(TL.tvLocID) AS varchar(10))
ELSE 'Multiple'
END AS tvLocID, 
CASE WHEN MIN(TL.City)= MAX(TL.City) THEN MIN(TL.City)
ELSE 'Multiple'
END AS City, 
T.Title 
FROM TV T INNER JOIN TVCat TC ON T.CatID = TC.tvCatID
INNER JOIN TVStationLocs TTL ON T.tvID = TTL.tvID
INNER JOIN TVLocs TL ON TTL.LocID = TL.LocID
GROUP BY T.tvID, T.tvCatID, TC.tvCat,T.Title
ORDER BY Title
END
SET QUOTED_IDENTIFIER OFF

Joker444
Starting Member

4 Posts

Posted - 2009-01-29 : 19:31:46

It worked great! Thanks a million!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-30 : 02:03:56

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources