a to b a to c procedure? 2

[codrutza]

Jobs.JobNo=Mov.JobNo and Jobs.JobNo=Cont.JobNo

Mov and Cont are not related
I wish to have for each Job No, all the movs, all the conts, just once. Can be this accomplished with stored procedures?

I get
Job No Mov No Cont No
1008826 R1008826 CU2991055
1008826 R1008826 CU3046948
1008826 R1008826 CU3113120
1008826 R1008826 CU1868004
1008826 S1008826 CU2991055
1008826 S1008826 CU3046948
1008826 S1008826 CU3113120
1008826 S1008826 CU1868004

I want
1008826 R1008826 CU2991055
S1008826 CU3046948
CU3113120
CU1868004

 

[bborissov]

You can't. This is a job for your front end to ignore repeated parts of the result set

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Catadmin]

You could concatenate the Movs and the Conts onto one record line, but bborissov is correct. For the format you're looking at, it has to be a report or an application end modification.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[codrutza]

Thank you very much for your replies.
Usually I do this in Crystal Reports using subreports, but I was wondering if there is another way.

 

[bborissov]

codrutza,
his is not possible and the main reason (I think) is that if you ignore repetitive values in the query and end with something like:

1008826     R1008826       CU2991055
            S1008826       CU3046948
                           CU3113120
1008827     R1008827       CU2991088
            S1008827       CU3046999
                           CU3113100

What will happen if you put ORDER BY JobNo, MoveNo, you will have a result like this:

                           CU3113120
                           CU3113100
            S1008826       CU3046948
            S1008827       CU3046999
1008826     R1008826       CU2991055
1008827     R1008827       CU2991088

and now how do you know which record for which jobno belongs

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP