Re: sql question

On 30 mayo, 16:49, "fitzjarr..._at_cox.net" <orat..._at_msn.com> wrote:
> On May 30, 1:58 pm, zamba <CRISTIAN_FERR..._at_hotmail.com> wrote:
>
>
>
> > Hi..i have this trouble ....
>
> > i have table A wich this data
>
> > cod_group bill_type
> > GROUPA BILLTYPE1
> > GROUPA BILLTYPE2
> > GROUPB BILLTYPE1
> > GROUPC BILLTYPE2
> > GROUPC BILLTYPE3
>
> > THEN i have 3 tables
>
> > TABLE NOTICE wich have this field : notice_number
>
> > TABLE NOTICE_BILLS wich have notice_number , bill_number
>
> > TABLE BILLS wich have bill_number , bill_type
>
> > i wish to get all the NOTICE wich have exactly the same bill type in
> > its bills that the group i send as parameter.If group have 2 types,
> > notice must have 2 bills with the same code, if group have 1 type,
> > notice must have 1 bill with this type
>
> > example
>
> > NOTICE 1
> > BILL100 (BILLTYPE1)
> > BILL200(BILLTYPE3)
>
> > IF I SEND PARAMETER GROUPA , GROUPB OR GROUPC NO ROWS MUST RETURN
> > because the combination doesn't exist in any group
>
> > if ihave
>
> > NOTICE2
> > BILL300(BILLTYPE1)
>
> > IF I SEND GROUPA , no rows must return, because group a have
> > BILLTYPE1 AND BILLTYPE2, but if i send GROUPB , must return a
> > value,
>
> > thanks !!!!!!
>
> It's too bad you didn't include the entire homework assignment in your
> post ...
>
> So, what have you tried to solve this problem? Post what you've done
> and we'll give advice. We won't do your assignment for you.
>
> David Fitzjarrell

Sorry David...i tried like this

SELECT ID_NOTICE FROM GCCB_NOTICE gnotice WHERE

(select count(*) from gccom_bill gb,GCCB_NOTICE_BILL NB   where nb.id_bill = gb.id_bill
    and gnotice.id_notice = nb.id_notice)     =
(select count(*) from gccom_bill gb, gcpr_print_bill_types_groups gp,GCCB_NOTICE_BILL NB
  where nb.id_bill = gb.id_bill
    and gb.bill_type (+) = gp.bill_type
    and gnotice.id_notice = nb.id_notice AND     gp.print_bill_type_group='GRPCTGE')

but the problem is like outer join doesn't work like i hope, this should bring 2 regs but brings only one (the one wich have the same bill_type)

gccb_notice = notice (example)
gccom_bill = bills (example)
gcpr_print_bill_types_groups = A (example) GCCB_NOTICE_BILL = NOTICE_BILLS (example) Received on Fri May 30 2008 - 15:07:24 CDT