sql question

#11

On May 30, 3:07*pm, zamba <CRISTIAN_FERR... (AT) hotmail (DOT) com> wrote:

Quote:

On 30 mayo, 16:49, "fitzjarr... (AT) cox (DOT) net" <orat... (AT) msn (DOT) com> wrote:

On May 30, 1:58 pm, zamba <CRISTIAN_FERR... (AT) hotmail (DOT) 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)- Hide quoted text -

- Show quoted text -

Provide the create statements for the tables involved and the data
you're using in those tables. We can't begin to assist you without
that information. But my first guess is you're making this MUCH too
difficult.

David Fitzjarrell

#12

On May 30, 3:07*pm, zamba <CRISTIAN_FERR... (AT) hotmail (DOT) com> wrote:

Quote:

On 30 mayo, 16:49, "fitzjarr... (AT) cox (DOT) net" <orat... (AT) msn (DOT) com> wrote:

On May 30, 1:58 pm, zamba <CRISTIAN_FERR... (AT) hotmail (DOT) 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)- Hide quoted text -

- Show quoted text -

Provide the create statements for the tables involved and the data
you're using in those tables. We can't begin to assist you without
that information. But my first guess is you're making this MUCH too
difficult.

David Fitzjarrell

#13

On May 30, 3:07*pm, zamba <CRISTIAN_FERR... (AT) hotmail (DOT) com> wrote:

Quote:

On 30 mayo, 16:49, "fitzjarr... (AT) cox (DOT) net" <orat... (AT) msn (DOT) com> wrote:

On May 30, 1:58 pm, zamba <CRISTIAN_FERR... (AT) hotmail (DOT) 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)- Hide quoted text -

- Show quoted text -

Provide the create statements for the tables involved and the data
you're using in those tables. We can't begin to assist you without
that information. But my first guess is you're making this MUCH too
difficult.

David Fitzjarrell

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

sql question

comp.databases.oracle.misc comp.databases.oracle.misc

Re: sql question - 05-30-2008 , 03:40 PM

Re: sql question - 05-30-2008 , 03:40 PM

Re: sql question - 05-30-2008 , 03:40 PM