Re: Not very good at SQL, trying to query multiple tables

Thank you for your reply, Daniel.

I am not only a bit of a SQL newbie but also a newcomer in this group. I was not asking anyone to do my work, I was asking for friendly assistance with a SQL query that I have struggled with for a number of days now.

I am sorry if I didn't provide the required information in my question.

However, as is often the case, trying to explain the problem to others also made it more clear to myself. I have been able to create a statement that seems to do the job. Here is what I ended up with:

select       RE.deptno,
                to_date(RE.begtime, 'YYYY-MM-DD'),
                RE.grade,
                RE.gradeweight,
                RE.reelno,
                SE.setno,
                MIN(RE.reellength),
                MIN(SE.setlength),
                MIN(RU.reelwidth),
                SUM(RO.rollwidth)

from          reelmaster RE,
                setdesc SE,
                rolldesc RO,
                runmaster RU

where        RE.deptno = 6
and           to_date(RE.begtime, 'YYYY-MM-DD') >= '2006-07-01'
and           to_date(RE.begtime, 'YYYY-MM-DD') <= '2006-07-31'
and           SE.reelno = RE.reelno
and           RO.reelno = SE.reelno
and           RO.setno = SE.setno
and           RU.runorder = SE.runorder

group by   RE.deptno,
                to_date(RE.begtime, 'YYYY-MM-DD'),
                RE.grade,
                RE.gradeweight,
                RE.reelno,
                SE.setno

Best regards,
Ture

DA Morgan skrev:

> ture_at_turedata.se wrote:
> > I know what I have and what I want but not how to get there. Can
> > someone suggest a SQL SELECT statement to give the desired result:
> >
> > Some background info:
> > I have four tables which contains some data about paper mill
> > production.
> > The paper is produced on wide and long rolls of paper called REELS.
> > After a REEL is produced, it is re-rolled and cut into shorter and less
> > wide SETS of ROLLS. There is also a table called RUNMASTER which
> > contains some information for a production period where several REELS
> > are produced.
> >
> > A REEL can typically be 6.7 meters wide and contain 27,222 meters of
> > paper.
> > The ROLLS from such a REEL could be:
> > SET 1: 10,000 meters long, 2.10, 2.10 and 2.10 meters wide. (0.4 meters
> > width waste)
> > SET 2: 10,000 meters long, 2.10, 2.10 and 2.10 meters wide. (0.4 meters
> > width waste)
> > SET 3: 7,000 meters long, 3.20 and 3.20 meters wide (0.3 meters width
> > waste)
> > (222 meters lenghth waste)
> >
> > RUNMASTER
> > runorder
> > reelwidth
> >
> > REELMASTER (several reels per runorder)
> > runorder
> > deptno
> > reelno
> > grade
> > gradeweight
> > reellength
> >
> > SETMASTER (several sets per reel)
> > reelno
> > setno
> > setlength
> >
> > ROLLMASTER (several rolls per set)
> > reelno
> > rollno
> > rollwidth
> >
> > This is the result I want:
> > For REELMASTER.DEPTNO = 6 and REELMASTER.BEGTIME >=2006-07-01 and
> > REELMASTER.BEGTIME <2006-08-01
> >
> > Grouped for each REELNO and SET
> >
> > REELNO, SETNO, to_date(BEGTIME), GRADE, GRADEWEIGHT, REELLENGTH,
> > SETLENGTH, REELWIDTH, sum(ROLLWIDTH)
> >
> > The resulting rows for the example reel I mentioned before would be:
> > 123456, 1, 2006-07-01, GRADEX, 150, 27222, 10000, 6,7, 6.3
> > 123456, 2, 2006-07-01, GRADEX, 150, 27222, 10000, 6,7, 6.3
> > 123456, 3, 2006-07-01, GRADEX, 150, 27222, 7000, 6,7, 6.4
> >
> > Best regards,
> > Ture Magnusson
> > Karlstad, Sweden
>
> If you want someone to do your work for you please provide the
> DDL to create the table, the insert statements, and example
> output.
> --
> Daniel A. Morgan
> University of Washington
> damorgan_at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org
Received on Tue Aug 01 2006 - 11:01:14 CDT