Pls help with this query - 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)

Pls help with this query

Author

Topic

superhero
Yak Posting Veteran

52 Posts

Posted - 2007-01-31 : 09:40:48

Hi everyone

I'm reading a billion books on SQL, so pls don't think I'm lazy for typing my query here, but I need to get this working asap and the books aren't helping!

Using SQL Enterprise 2000, I need to show results in VB.net 2.0. My query:

Two tables: [containers] and [indexdata]
These tables are joined by field 'containerid'
For each entry in [containers] I have about 200 - 300 entries in [indexdata]
I would like to query the db and get a list of all container codes (field 'containercode' in [containers]) meeting a where clause that I will specify, and next to each container code, have the number of indexdata files corresponding to it.

Eg. If I had a container number 1, 7 and 9 in [containers] and 200, 100 and 50 records respectively corresponding to it in [indexdata] I would like to show in the datagridview:

CONTAINER CODE | NUMBER OF FILES
----------------------------------
1 | 200 FILES
7 | 100 FILES
9 | 50 FILES

I can do this with a looooong loop, but I'm using webservices, so I can't make too many back and forward queries to the database, I would like 1 query that will do the job and return the data to the client. Is this possible?

Thanks in adavance - Hopefully soon I'll be clever enough to actually contribute to this forum!

D.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-31 : 09:51:06

select containercode, cast(sum(files) as varchar) + 'files'
from sometable
group by containercode
order by containercode

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-31 : 09:51:28

[code]Select c.containerid as [Container Code], cast(count(i.*) as varchar(10)) + ' Files' as [Number of Files]
from containers c
join indexdata i
on c.containerid = i.containerid
Where <your-where-condition>
group by c.containerid[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-31 : 09:52:52

[code]
select c.containercode, number_of_files = count(*)
from containers c inner join indexdata i
on c.containerid = i.containerid
where containercode in (1, 7, 9)
group by c.containercode
[/code]

superhero
Yak Posting Veteran

52 Posts

Posted - 2007-01-31 : 09:55:25

Thanks a 'mil guys, that's EXACTLY what I wanted. Slowly getting there!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-31 : 11:28:30

A good thing to learn at this point is: don't return a varchar with the count concatenated with the word "Files". Simply return the count as an integer, and format it the way you want in VB. Otherwise, if your VB app cares about how many files there are, it needs to parse the varchar to determine the count. Or, if you want to display it differently in other places, you'll need a different SQL statement. Always return raw DATA in SQL and do your formatting at the front end.

- Jeff
http://weblogs.sqlteam.com/JeffS

superhero
Yak Posting Veteran

52 Posts

Posted - 2007-01-31 : 14:40:35

Peter

After trying your code SQL formatted it to look like this in the query viewer:

SELECT containers.containercode, CAST(SUM(indexdata.containerid) AS varchar) + 'files' AS Expr1
FROM containers CROSS JOIN
indexdata
GROUP BY containers.containercode
ORDER BY containers.containercode

When I run it, it executes fine (I changed the Select bit to have 'indexdata.containerid' in it - think it's maybe wrong. Anyways, when I run it, it returns results but it's not the right number of files in the box. It's way out - returns 2108 files when there's only 20.

What am I missing here?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-31 : 14:48:23

Do not CROSS JOIN. Do a INNER JOIN as described by the others...

Peter Larsson
Helsingborg, Sweden

superhero
Yak Posting Veteran

52 Posts

Posted - 2007-01-31 : 17:01:48

Thanks Peter, I modified khtan's code to this:

SELECT c.containercode AS [Container Code], CAST(COUNT(c.containercode) AS varchar(10)) + ' Files' AS [Number of Files]
FROM containers c INNER JOIN
indexdata i ON c.containerid = i.containerid
GROUP BY c.containercode

and it worked perfectly.

Thanks a lot!

Dawie

superhero
Yak Posting Veteran

52 Posts

Posted - 2007-02-02 : 03:05:14

Peter / Khtan

This is the code I'm using now:

SELECT c.containercode AS [Container Code], CAST(COUNT(c.containercode) AS varchar(10)) AS [Number of Files]
FROM containers c INNER JOIN
indexdata i ON c.containerid = i.containerid
WHERE (c.userid = <int>) 'user supplied int
GROUP BY c.containercode

Would it be possible to modify the code to also return the 'dod' and 'userid' fields in the containers table, whenever I add anything else to the select line it says it's not part of the GROUP BY clause.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-02 : 03:18:15

quote:
[cdoe]CAST(COUNT(c.containercode) AS varchar(10)) AS [Number of Files][/code]

Do you have to convert the count to string ? In your previous post you wanted the count to be formatted to like 'xxx FILES'. But from the code you posted, it did not concatenate with string. So do you still required the count to be return to your application in string ?

quote:
Would it be possible to modify the code to also return the 'dod' and 'userid' fields in the containers table

Yes. But we need to know column dod is from which table ? Please post the table structure, some sample data and result that you want.

superhero
Yak Posting Veteran

52 Posts

Posted - 2007-02-02 : 04:15:27

Hi

I just need the number of files per container, doesn't have to be string, as long as I get the number - I can format the raw data in .net

The structure of the [containers] table is as follows:
containerid <numeric><auto increment> <9>
containercode <nvarchar><12>
containerdesc <nvarchar><50>
responsible <nvarchar><50>
dod <datetime><8>
dept <numeric><9>
companyid <numeric><9>
subcoid <numeric><9>
createdate <datetime><8>
userid <numeric><9>
containerstatus <numeric><9>

The structure of the [indexdata] is as follows:
indexid <numeric><9>
containerid <numeric><9> (JOINED WITH containerid IN [containers]
indexfield1 <nvarchar><255>
indexfield2 <nvarchar><255>
indexfield3 <nvarchar><255>
indexfield4 <nvarchar><255>
indexfield5 <nvarchar><255>
indexfield6 <nvarchar><255>
indexfield7 <nvarchar><255>
dod <nvarchar><255> (this is another dod field, not related to the [containers]dod field.
fileno <nvarchar><9>
userid <numeric><9>
filestatus <numeric><9>

There's another table, [subco] with this layout
subcoid <numeric><auto increment><9>
oneilno <nvarchar><10>
subconame <nvarchar><50>

subcoid joins in [containers] and [subco]

I would like to return the following data to my .net app in a dataset - I'll format it from there. I'll type the table-name in [square brackets] and field name in {curly brackets}

[containers]{containercode}, [indexdata]number of files in container, [containers]{dod}, [subco]{subconame}, [indexdata] number of characters in the following fields (indexfield1, indexfield2, indexfield3, indexfield4, indexfield5, indexfield6, indexfield7, dod, fileno)

The code below is what I currently use to calculate number of characters in a specific container:
CREATE Procedure CountCharactersInContainer
(@containercode nvarchar(15), @sum int output)
as

SELECT @sum = SUM(LEN(containercode) + LEN(indexfield1) + LEN(indexfield3) + LEN(indexfield4) + LEN(indexfield5) + LEN(indexfield6) + LEN(indexfield7) + LEN(dod) + LEN(fileno))
FROM indexdata JOIN containers
ON indexdata.containerid = containers.containerid
WHERE containercode = @containercode
GO

Geeez...that's it - is it at all possible to get all of this info all at once?

If you could help me with this I would be forever greateful!

D.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-05 : 03:37:22

Try this, and filter out the invalid containercodes at frontend app.

CREATE Procedure CountCharactersInContainer
as 

SELECT	containercode,
		SUM(LEN(containercode) + LEN(indexfield1) + LEN(indexfield3) + LEN(indexfield4) + LEN(indexfield5) + LEN(indexfield6) + LEN(indexfield7) + LEN(dod) + LEN(fileno))
FROM		indexdata
INNER JOIN	containers ON indexdata.containerid = containers.containerid
GROUP BY	containercode
ORDER BY	containercode

Peter Larsson
Helsingborg, Sweden

superhero
Yak Posting Veteran

52 Posts

Posted - 2007-02-05 : 04:15:32

Thanks Peter

Khtan helped me to get a dataset from SQL with the container code and next to it the number of files in the container (as found in the indexdata-table).

So showing that in a datagrid gives me a list of container codes and next to each container code, the number of files in it.

You helped me with SQL statement to get container code and number of characters in that container as found in indexata-table.

Now what I need is to show all of this simultaneously, ie:

container code, number of files linked to container as found in indexdata, number of characters in index data, date created (the dod-field in [containers]) and company name (subconame in [subco])

See, in layman's terms I would put it like this:

Select containers.containercode, {expression to get the total number of files found in [indexdata]}, {expression to get total number of characters found in [indexdata]}, containers.dod, subco.subconame, users.userid from containers, indexdata, subco where containers.containerid = indexdata.containerid and containers.subcoid = subco.subcoid and indexdata.filestatus = 1 and containers.userid = users.userid and indexdata.indexdate >= #xx/xx/xxxx# and indexdate <= #xx/xx/xxxx#

superhero
Yak Posting Veteran

52 Posts

Posted - 2007-02-06 : 06:19:05

Figured it out! If anyone ever needs to do anything like this, use the following as a guideline.

SELECT c.containercode AS [Container Code], CAST(COUNT(c.containercode) AS varchar(14)) AS [Number of Files], SUM(LEN(c.containercode)
+ LEN(i.indexfield1) + LEN(i.indexfield2) + LEN(i.indexfield3) + LEN(i.indexfield4) + LEN(i.indexfield5) + LEN(i.indexfield6) + LEN(i.indexfield7)
+ LEN(i.dod) + LEN(i.fileno)) AS [Number of Characters], c.createdate AS [Create Date For Container], s.subconame AS [Company Name]
FROM containers c INNER JOIN
indexdata i ON c.containerid = i.containerid INNER JOIN
subco s ON c.subcoid = s.subcoid
WHERE (c.userid = 1)
GROUP BY c.containercode, c.createdate, s.subconame
ORDER BY c.createdate

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources