combining records and determining record count

combining records and determining record count

Post by ro » Fri, 22 Mar 2002 03:32:38



please help me.

the sample database - contains a main table (tblMain) with a couple
fields. the tblProduct table is a reference table containing a list of
products. the tblMain_Product table is a join table that links these 2
tables together. my actual database has a few of these tables, but for
simplicity, i've only included 1 such relationship.

the problem - i need to query the database and bring back all fields
for matching records. then they need to be displayed in an html table.

current solution - i'm querying the database like this:
select tblMain.ID,fld1,fld2,Product
from tblMain
left join tblMain_Product tblMainp on tblMain.ID=tblMainp.tblMain_id
left join tblProduct p on tblMainp.Product_id=p.ID
order by Product

which returns this:
ID       fld1   fld2     Product              
-------- ------ -------- --------
1        x      y        product1
2        a      b        product2
1        x      y        product3
3        c      d        product4

there are a couple problems with this:
1. i need to display a single record on a single line. the above gives
me 2 lines with ID 1, but i need to show 1.
like this: 1   x    y    product1,product2
currently i'm doing a lot of processing in asp to combine records with
the same ID. it involves filtering the recordset, looping, etc. if you
want that code, i can post it, but i think it's a pretty hacky way to
do it.

2. i can't get an accurate record count until i've combined the
records. the above query would give me a record count of 4, but there
are really only 3 unique ID's. ideally i would like to limit the
amount of data i return, but can't really, because initially, i don't
know how much i have. a top 3 in the select statement would limit the
data returned, but i wouldn't get the top 3, just 2.

i can work around number 1, but number 2 is a problem. if anyone has
any suggestions, please let me know. server side, client side,
database layer, database design. anything. thanks for your time.

a couple other things, the sql needs to be generated at run-time and
the returned recordset can be sorted by a number of different fields
(also determined at run-time).

btw, i'm using ms sql2000, ado 2.7, and asp.

here's the code to create the sample tables and data:

CREATE TABLE [tblMain] (
        [ID] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,
        [fld1] [varchar] (100) NULL ,
        [fld2] [varchar] (100) NULL
)
GO

CREATE TABLE tblProduct (
        [ID] [smallint] IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,
        [Product] [varchar] (200) NULL
)
GO

CREATE TABLE [tblMain_Product] (
        [tblMain_id] [int] NOT NULL ,
        [Product_id] [smallint] NOT NULL
)
GO

ALTER TABLE [tblMain_Product] WITH NOCHECK ADD
        CONSTRAINT [PK_tblMain_Product] PRIMARY KEY  CLUSTERED
        (
                [tblMain_id],
                [Product_id]
        )
GO

ALTER TABLE [tblMain_Product] ADD
        CONSTRAINT [FK_tblMain_Product_tblMain] FOREIGN KEY
        (
                [tblMain_id]
        ) REFERENCES [tblMain] (
                [ID]
        ) ON DELETE CASCADE  ON UPDATE CASCADE ,
        CONSTRAINT [FK_tblMain_Product_tblProduct] FOREIGN KEY
        (
                [Product_id]
        ) REFERENCES tblProduct (
                [ID]
        )
GO

insert tblMain VALUES ('x','y')
insert tblMain VALUES ('a','b')
insert tblMain VALUES ('c','d')

insert tblProduct VALUES ('product1')
insert tblProduct VALUES ('product2')
insert tblProduct VALUES ('product3')
insert tblProduct VALUES ('product4')

insert tblMain_Product VALUES (1,1)
insert tblMain_Product VALUES (1,3)
insert tblMain_Product VALUES (2,2)
insert tblMain_Product VALUES (3,4)

 
 
 

1. How to determine record count and move forward and backwards in the recordset

I am calling the stored procedure OpenTimeInvoices using the code listed
below.
I want to execute one set of code if records exist and another set of code
if there
are no records in the query.  I also want to be able to move forward and
backwards
in the recordset.  How can I do this?
=================================
Dim CmdSP,RetVal,Rs,PrtDate
Dim adCmdSPStoredProc,adParamReturnValue
Dim adParaminput,adParamOutput,adVarChar
adCmdSPStoredProc = 4
adParamReturnValue = 4
adParaminput = 1
adParamOutput = 2
adVarChar = 200
adInteger = 3
set CmdSP = Server.CreateObject("ADODB.Command")
CmdSP.ActiveConnection = Application("MyConnection")
CmdSP.CommandText = "OpenTimeInvoices"
CmdSP.CommandType = adCmdspStoredProc
cmdsp.Parameters.Append cmdsp.CreateParameter("return", adInteger,
adParamReturnValue,4)

adParaminput,7,clid)
set Rs = CmdSP.execute

if rs.bof <> rs.eof then
 call show_records()
else
 Response.Write "no records"
end if

2. DAO: So little code, so many errors

3. Determining Record Count

4. Please Repost This to CompuServe-foxpro

5. combine many records into one and one record into many

6. Is it possible to "Reverse-Crosstab" a row returned by a query

7. Combining multiple selected records into a single record

8. 34643-MN-MINNEAPOLIS-DBA Skills-ORACLE-Oracle DBA's

9. Combining multiple records of same types into one record

10. Determine number of record and record size

11. Counting Records in Table not Connected to Records in Another (SQL Newbie)

12. counting record only when field value is not equal to value of previous record

13. counting records only when value is different than previous record (MSSQL 2000)