Re: Real dificult SP ... is it even possible ?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Zwi2000 (zwi2000_at_hotmail.com)
Date: 07/20/04 

Date: Tue, 20 Jul 2004 00:23:13 -0400

This is my actual query:

Select a.attsid, a.firmid, a.caseid, a.userid, b.id, b.caseid from atts as a
inner join cases b on a.caseid = b.id
group by b.id,a.attsid, a.firmid, a.caseid, a.userid, b.caseid

This are the results:

7 1 8 3 8 H1b-34234
17 1 20 3 20 DEMO0021H
15 1 18 3 18 L-34234
16 1 19 3 19 StandardExp2121
18 1 20 7 20 DEMO0021H
19 1 20 8 20 DEMO0021H
20 1 20 6 20 DEMO0021H
21 1 8 8 8 H1b-34234
22 1 8 6 8 H1b-34234

Instead I need this:

a.attsid a.firmid a.caseid a.userid b.id b.caseid
15 1 18 3 18
L-34234
16 1 19 3 19
StandardExp2121
18 1 20 7,8,6,3 20 DEMO0021H
7 1 8 3,8,6 8
H1b-34234

Like if they were grouped by caseid

Z

"Zwi2000" <zwi2000@hotmail.com> wrote in message
news:OTtTm0gbEHA.3420@TK2MSFTNGP12.phx.gbl...
> Hi,
>
> I have been thinking and thinking how to do this query, but just cant
think
> of anything. Ill try to explain in steps.
>
> I have two pages:
>
> 1) Page one is a search page that will pass on parameters to page two. I
> will focus on parameters for "UserId", it passes either one integer (2),
or
> many depending on the user selection (2,5,18,2).
> 2) Page two should display the results and here is where the query is
> needed.
>
> This is the design of the tables.
>
> 1) Table one has "cases", each case has a unique ID. It could be as simple
> as:
>
> CaseID - Integer (Key)
> CaseNumber - varchar
>
> 2) Table two links users to cases (Users is another table, at this point
> doesnt matter)
>
> LinkId - Integer (Key)
> UserId - Integer (This links Users here)
> CaseId - Integer (This is the key from table 1)
>
> I need to display on page two all cases in which the selected users are
> passed on from page # 1 (Could be one or more).
>
> If I link both tables I will get only one user for each case, and the case
> may be repeated. How do I display all users linked to one case in just one
> results (one line). so that when checked against the parameters I get the
> needed results.
>
> Something like:
>
> CaseId, UserId1 (Linked), UserId2 (Linked), UserId 4 (Linked), etc ....
>
> I tried:
>
> Select * from cases
> Inner join users on cases.userid = users.userid
>
> Problem: I get results like
>
> CaseId = 1221
> UserId = 3
>
> CaseId = 1221
> Userid =5
>
> And so forth .. one line each, what I need is:
>
> CaseId = 1221
> UserId = 3,5 ...
>
> That way I can run the desired query.
>
> Any idea how to accomplish this ?
>
> Thanks !
>
> Z
>
>
>
>
>

Relevant Pages

  • Re: correlated subquery in the crosstab
    ... FROM (tblStudent INNER JOIN (tblClass INNER JOIN ... values from the outer query and I have made an alias for it. ... I have students, courses, exam groups containing exams of courses, ... that the crosstab query in access has much more capablities than ...
    (microsoft.public.access.queries)
  • Re: correlated subquery in the crosstab
    ... I solved my problem using stored queries to act as subqueries. ... FROM (tblStudent INNER JOIN (tblClass INNER JOIN ... values from the outer query and I have made an alias for it. ... that the crosstab query in access has much more capablities than ...
    (microsoft.public.access.queries)
  • Re: Real dificult SP ... is it even possible ?
    ... > I have been thinking and thinking how to do this query, ... > many depending on the user selection. ... > I need to display on page two all cases in which the selected users are ... > Inner join users on cases.userid = users.userid ...
    (microsoft.public.sqlserver.programming)
  • Re: correlated subquery in the crosstab
    ... The first query ... TRANSFORM FirstAS FirstOfscore ... FROM tblStudent INNER JOIN (((tblEduYear INNER JOIN tblExamGrp ON ... that the crosstab query in access has much more capablities than ...
    (microsoft.public.access.queries)
  • Re: Matching records for an update query
    ... then the update query would look something like ... UPDATE RegisteredMembers INNER JOIN BusinessChanges ...
    (microsoft.public.access.queries)