Need help with SQL to create a recordset. Can't work it out.

[WuCOng]

I'm struggling with a piece of SQL. I need to create a recordset with two columns, "name" and "number of comments".

The tables are designed like this:

Service (table):
Fields are: ServiceID (primary key), name, servicetype.

Comments (table):
Fieldsare: CommentID (primary key), ServiceID (foreign key from Service table), comment.

I have an ASP page which delivers the servicetype (from a form) to the next page.

I need to use this servicetype to query the Service and Comments table so I get a new recordset that holds the "name" of each service where "servicetype" = "servicetype from the form", and which tells me how many (or what % of the total) comments there are about that particular service.

I'm considering removing the "ServiceID" field and making the "name" of each the primary key as the names are all unique anyway.

So far I'm stumpted at: SELECT name, COUNT(comments) FROM Service, Comments WHERE servicetype = service_type(from form). Can I use Oracle type assignments in an Access DB, which these tables are in, of the form SELECT s.name, c.comments FROM service s, comments c . . . IF I change the primary key I think I will have to do this because both Service and Comments tabels will have a "name" feild.

Any SQL experts out there?

 

[SauerC]

Yes, you can do table and field aliases in Access

I think you are looking for is:

select s.name, count(c.commentID)
from service s, comments c
where s.serviceid = c.serviceid
and s.servicetype = service_type(from form)
group by s.name

that way, you will get every service name with the counts of the comments on that service name for whatever servicetype you specify

-Christian

 

[WuCOng]

Thanks Christian. You're a star.