Returning specific data - 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)

Returning specific data

Author

Topic

huge
Starting Member

31 Posts

Posted - 2003-03-12 : 10:24:13

Hey guys, ok well its been a while since my last SQL project, I now have a small problem but I think its simply from my lack of remembering what to do...

I have 2 tables STN and images

STN
-----------
STN_key (PK)
STN_Description (description of station)
STN_Number (name given for each station)
Job_Number (each job has n number of STN's)

images
-----------
imageid (PK)
blob (image)
... (some non_relevant stuff for use with image)
STN_id (FK to STN.STN_key)
uploaded_date

anyway I am doing a search on this and there are 3 possible ways a user can search. Either With all job #'s and therefore all STN's, either with a specific Job # and all associated STN's with that job, or either with a specific job and STN #.

Now what I want to return to my front end is
1) Number of images
2) all relevant job #s (depending on which search method)
3) all relevant STN #s (depending on which search method)
4) uploaded date
5) STN description

now once I know how to do this I am fine but I am kind of confused as to how Im going to do this. I was thinking of using a temporary table and putting all STN_key's in first, then counting the # of images that use that key, and pulling the job # and STN. Now this is where I run into a problem. If I put the STN_key;s in there how will I iterate through them. Taking the first getting the info, filling the rest of the temp table, then getting the next one. Are the rows of a table referencable (not a word heh) by an index number or something similar?

PLease help, im thinking waay too much on this ;)

Thanks
HuGE

SamC
White Water Yakist

3467 Posts

Posted - 2003-03-12 : 11:32:45

create procedure dbo.thisishowiddoit

@job int = null ,
@stn int = null
as

SELECT *
FROM STN S
INNER JOIN images I ON I.STN_id = S.STN_key
WHERE S.Job_Number = IsNull(@job, S.Job_Number)
AND S.STN_Number = IsNull(@stn, S.STN_Number)

RETURN(@@ROWCOUNT) -- Returns number of rows (images??)
GO

-- This gives the recordset for any of the 3 possible queries

Your requirements are not completely clear to me, but this query seems to give all 5 elements you requested. Element (1) is a scalar count of the number of images. I've set it as a return value.

Elements 2 through 5 are row by row, with the exception of the job# which is repeated on several rows. If you wanted to return a second recordset with only the distinct job numbers you could like this:

SELECT Job_Number, count(*) as STNcount
FROM STN S
INNER JOIN images I ON I.STN_id = S.STN_key
WHERE S.Job_Number = IsNull(@job, S.Job_Number)
AND S.STN_Number = IsNull(@stn, S.STN_Number)
GROUP BY Job_Number

This recordset returns unique job numbers and the count of rows (STN_Number) for each job.

HTH

Sam

huge
Starting Member

31 Posts

Posted - 2003-03-12 : 12:45:31

Thanks man, this is perfect, alot of stuff you addressed were questions I had. Thanks again

hUGE

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources