Post reply

GROUPING QUERY

  • March 25, 2004 at 5:55 pm

    #107465

    hI FRIENDS

    I HAVE DATA LIKE FOLLOWING

    Master      Type

    1111111111 Avail

    1111111111 Meet

    1111111111 Oper

    1111111111 TD  

    1210      Avail

    1210      Oper

    1269      Avail

    4532      GC  

    4532      JA  

    4532      Avail

    4532      Oper

    I WANT FINAL OUTPUT LIKE BELOW

    Master      Type

    1111111111 Avail

    1111111111 Oper

    4532      Avail

    4532      Oper

    I.E WHERE MASTER HAVING TYPE "Avail" AND "Oper"

    tHANKS

  • March 25, 2004 at 6:07 pm

    #500535

    Should result include 1210 as well?

  • March 25, 2004 at 6:11 pm

    #500537

    This should do it but might be a better way I didn't see.

    SELECT

     x1.master,

     x1.type

    FROM

     tblX x1

    INNER JOIN

     (

      SELECT

       x2.master,

       x2.type t1,

       x3.type t2

      FROM

       tblX x2

      INNER JOIN

       tblX x3

      ON

       x2.master = x3.master

      WHERE

       x2.type = 'avail' and

       x3.type = 'oper'

    ) o1

    ON

     x1.master = o1.master and

     x1.type in (o1.t1, o1.t2)

  • March 25, 2004 at 6:11 pm

    #500536

    sorry abv 1210 has type "Oper" only

    i wrongly typed two different values.

    now 1210 should not b listed.

    i want only master records that r type "Avail" and "Oper"

    Thanks

  • March 25, 2004 at 6:18 pm

    #500541

    Thanks for query Antares686

    can u explain me what's u r doing query plz.

    Thanks

  • March 25, 2004 at 6:27 pm

    #500545

    Hi friend

    I tried ur query in foxpro 8 it return syntax error

  • March 25, 2004 at 6:28 pm

    #500546

    select a.* from test a

     where exists(select * from test b where type = 'Avail' and a.master=b.master)

     and exists(select * from test c where type='oper'and a.master=c.master)

     and type in ('Avail', 'Oper')

  • March 25, 2004 at 6:32 pm

    #500548

    Sure first the subquery

      SELECT

       x2.master,

       x2.type t1,

       x3.type t2

      FROM

       tblX x2

      INNER JOIN

       tblX x3

      ON

       x2.master = x3.master

      WHERE

       x2.type = 'avail' and

       x3.type = 'oper'

     

    Here I am join all master items in the table to themselves thru a self referential join scenario. Then I just look where the condition for first side has the type avail and the second side has type oper as both are required. What this gives me is only the masters that have both types in the table (could have done x2 type as oper and x3 as avail but you have to check both and not both ways or you just get extra data).

     

    From there I again join the table to itself in the outer join but I have figured out the masters I need from subquery and just to make things simple (for me) I join the types with the in clause to get both records.

    Note however I could have just returned master from the subquery and join to itself in the outer query on master only, then I would have had to done

    x1.type in ('avail','oper')

    or removed from the on and used in a where clause

    WHERE x1.type in ('avail','oper')

    I just chose the way I did, would have to use QA to see which has the best execution plan but all may be about the same except my way may use a bit more memory and space in the tempdb for the subqueries output.

  • March 25, 2004 at 6:36 pm

    #500551

    DId it say where the error was as I am working on notepad not QA right now and no test system here.

  • March 25, 2004 at 6:37 pm

    #500552

    Because of the way exists works with regards to each record entering the buffer it will take a long time on large datasets.

  • March 25, 2004 at 6:40 pm

    #500553

    i cannot use following query bcoz

    select a.* from test a  where exists(select * from test b where type = 'Avail' and a.master=b.master)  and exists(select * from test c where type='oper'and a.master=c.master)  and type ...

    foxpro does not support exists keyword

  • March 25, 2004 at 6:42 pm

    #500556

    Try my query in QA and see if it works. If does then it may be another foxpro limitation you hit. I would then consider wrapping logic in SP and tryign that way.

  • March 25, 2004 at 6:42 pm

    #500555

    Hi Antares686

    i tried like following .here ; is line continuation

    SELECT x1.master, x1.type FROM BOOKING x1 ;

    INNER JOIN (SELECT x2.master,x2.type t1,x3.type t2 FROM  BOOKING x2 ;

    INNER JOIN BOOKING x3 ON x2.master = x3.master WHERE (UPPER(x2.type) = 'AVAIL' and UPPER(x3.type) = 'OPER')) o1 ;

    ON x1.master = o1.master and x1.type in (o1.t1, o1.t2)

     

    i am getting error on following line

    INNER JOIN (SELECT x2.master,x2.type t1,x3.type t2 FROM  BOOKING x2 ;

     

  • March 25, 2004 at 6:45 pm

    #500558

    Hi friends

    i got it.i tried like

     

    sele master,sum(iif(upper(type)="AVAIL",1,0)) as AvailN,;

     sum(iif(upper(type)="OPER",1,0)) as OperN ;

     group by 1 from booking ;

     where upper(type)="AVAIL" or upper(type)="OPER" ;

     having AvailN>0 and AvailN=OperN into cursor curTemp nowait

    sele master,type from booking where (upper(type)="AVAIL" or upper(type)="OPER") ;

     and master in (sele master from curTemp) nowait

     

    it works

  • March 25, 2004 at 6:45 pm

    #500557

    I don't use FoxPro myself but can you post the few lines before and after this so I can see if I can figure it out? Otherwise Stored Procedure may be your best friend.

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply