GROUPING QUERY

Question

Post reply

GROUPING QUERY

sqlpro

Hall of Fame

Points: 3859
More actions
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

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

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

wz700 SSCertifiable Points: 7133 More actions · Answer 1

wz700

SSCertifiable

Points: 7133

March 25, 2004 at 6:07 pm

#500535

Should result include 1210 as well?

Antares686 SSC Guru Points: 125444 More actions · Answer 2

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)

sqlpro Hall of Fame Points: 3859 More actions · Answer 3

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

sqlpro Hall of Fame Points: 3859 More actions · Answer 4

Thanks for query Antares686

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

Thanks

sqlpro Hall of Fame Points: 3859 More actions · Answer 5

Hi friend

I tried ur query in foxpro 8 it return syntax error

wz700 SSCertifiable Points: 7133 More actions · Answer 6

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')

Antares686 SSC Guru Points: 125444 More actions · Answer 7

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.

Antares686 SSC Guru Points: 125444 More actions · Answer 8

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

Antares686 SSC Guru Points: 125444 More actions · Answer 9

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

sqlpro Hall of Fame Points: 3859 More actions · Answer 10

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

Antares686 SSC Guru Points: 125444 More actions · Answer 11

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.

sqlpro Hall of Fame Points: 3859 More actions · Answer 12

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 ;

sqlpro Hall of Fame Points: 3859 More actions · Answer 13

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

Antares686 SSC Guru Points: 125444 More actions · Answer 14

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.