Discussion:
A Query
(too old to reply)
Dorian Chalom
2004-07-22 15:07:11 UTC
Permalink
OK I need this query designed....

Students
1234 Joe Blow

Courses
123 Math
234 Science
345 English

Books
1234 123 12 Algebra
1234 123 23 Algebra Workbook
1234 234 34 The Earth
345 45 Reading
345 56 Writing

TimeTable
1234 123
1234 234
1234 345



And I need a final table of :
123 12 Algebra
123 23 Algebra Workbook
234 34 The Earth
345 45 Reading
345 56 Writing

Can someone help?
sp
2004-07-22 18:17:24 UTC
Permalink
If your output is from the data available under "Books" table, how are the
other tables related to this output?

sp
Post by Dorian Chalom
OK I need this query designed....
Students
1234 Joe Blow
Courses
123 Math
234 Science
345 English
Books
1234 123 12 Algebra
1234 123 23 Algebra Workbook
1234 234 34 The Earth
345 45 Reading
345 56 Writing
TimeTable
1234 123
1234 234
1234 345
123 12 Algebra
123 23 Algebra Workbook
234 34 The Earth
345 45 Reading
345 56 Writing
Can someone help?
Dorian Chalom
2004-07-22 18:31:13 UTC
Permalink
I do need the student and course infor from thise tables but for this
example i did not include it.
Post by sp
If your output is from the data available under "Books" table, how are the
other tables related to this output?
sp
Post by Dorian Chalom
OK I need this query designed....
Students
1234 Joe Blow
Courses
123 Math
234 Science
345 English
Books
1234 123 12 Algebra
1234 123 23 Algebra Workbook
1234 234 34 The Earth
345 45 Reading
345 56 Writing
TimeTable
1234 123
1234 234
1234 345
123 12 Algebra
123 23 Algebra Workbook
234 34 The Earth
345 45 Reading
345 56 Writing
Can someone help?
Rick Bean
2004-07-22 20:12:34 UTC
Permalink
Dorian,
Since everything appears to be in the Books table, why not just use it or use an SQL Select to just get the fields you want. e.g.
SELECT coursePK, bookPK, bookTitle ;
From Books ;
Into Table Final

Rick
Post by Dorian Chalom
OK I need this query designed....
Students
1234 Joe Blow
Courses
123 Math
234 Science
345 English
Books
1234 123 12 Algebra
1234 123 23 Algebra Workbook
1234 234 34 The Earth
345 45 Reading
345 56 Writing
TimeTable
1234 123
1234 234
1234 345
123 12 Algebra
123 23 Algebra Workbook
234 34 The Earth
345 45 Reading
345 56 Writing
Can someone help?
Dorian Chalom
2004-07-23 15:31:13 UTC
Permalink
OK obviously I missed something in my explanation. Let me try again...

Here is my current Query:
SELECT CrsDemo.crsnumber, CrsDemo.crsname, ;
dbfCrsBk.BookNum, dbfCrsBk.Descript, ;
dbfCrsBk.Condition, StudTTbl.IDStudent, ;
CrsDemo.IDCourse, dbfCrsBk.IDBook, ;
dbfCrsBk.IDBook AS IDBook2, ;
dbfCrsBk.Condition AS Condition2 ;
FROM StudTTbl ;
INNER JOIN CrsDemo ;
ON StudTTbl.idcourse = Crsdemo.idcourse ;
LEFT OUTER JOIN dbfCrsBk ;
ON StudTTbl.idstudent = dbfCrsBk.idstudent ;
AND StudTTbl.idcourse = dbfCrsBk.idcourse ;
INTO DBF tdStdCrs ;
WHERE StudTTbl.idstudent = ?cIDStudent ;
GROUP BY CrsDemo.CrsNumber

But my problem is some classes may have two books requeried for them and may
not yet be assigned to the student so how do I get the query to include the
two books requeried for the class?


Thanks for being understanding and trying to help me.

"Rick Bean" <***@unrealmelange-inc.com> wrote in message news:***@tk2msftngp13.phx.gbl...
Dorian,
Since everything appears to be in the Books table, why not just use it or
use an SQL Select to just get the fields you want. e.g.
SELECT coursePK, bookPK, bookTitle ;
From Books ;
Into Table Final

Rick
Post by Dorian Chalom
OK I need this query designed....
Students
1234 Joe Blow
Courses
123 Math
234 Science
345 English
Books
1234 123 12 Algebra
1234 123 23 Algebra Workbook
1234 234 34 The Earth
345 45 Reading
345 56 Writing
TimeTable
1234 123
1234 234
1234 345
123 12 Algebra
123 23 Algebra Workbook
234 34 The Earth
345 45 Reading
345 56 Writing
Can someone help?
Anders
2004-07-27 08:03:14 UTC
Permalink
The GROUP BY is illegal (and superfluous). Before VFP8 we could get away
with GROUP BY clauses the break the rules. The Standard SQL rule is that the
GROUP BY clause and the SELECT clause must list the same columns, except for
aggregates like COUNT(*) or SUM(somecolumn) or MAX().
You don't have any aggregates at all, so you don't need a GROUP BY clause at
all.
-Anders
Post by Dorian Chalom
OK obviously I missed something in my explanation. Let me try again...
SELECT CrsDemo.crsnumber, CrsDemo.crsname, ;
dbfCrsBk.BookNum, dbfCrsBk.Descript, ;
dbfCrsBk.Condition, StudTTbl.IDStudent, ;
CrsDemo.IDCourse, dbfCrsBk.IDBook, ;
dbfCrsBk.IDBook AS IDBook2, ;
dbfCrsBk.Condition AS Condition2 ;
FROM StudTTbl ;
INNER JOIN CrsDemo ;
ON StudTTbl.idcourse = Crsdemo.idcourse ;
LEFT OUTER JOIN dbfCrsBk ;
ON StudTTbl.idstudent = dbfCrsBk.idstudent ;
AND StudTTbl.idcourse = dbfCrsBk.idcourse ;
INTO DBF tdStdCrs ;
WHERE StudTTbl.idstudent = ?cIDStudent ;
GROUP BY CrsDemo.CrsNumber
But my problem is some classes may have two books requeried for them and may
not yet be assigned to the student so how do I get the query to include the
two books requeried for the class?
Thanks for being understanding and trying to help me.
Dorian,
Since everything appears to be in the Books table, why not just use it or
use an SQL Select to just get the fields you want. e.g.
SELECT coursePK, bookPK, bookTitle ;
From Books ;
Into Table Final
Rick
Post by Dorian Chalom
OK I need this query designed....
Students
1234 Joe Blow
Courses
123 Math
234 Science
345 English
Books
1234 123 12 Algebra
1234 123 23 Algebra Workbook
1234 234 34 The Earth
345 45 Reading
345 56 Writing
TimeTable
1234 123
1234 234
1234 345
123 12 Algebra
123 23 Algebra Workbook
234 34 The Earth
345 45 Reading
345 56 Writing
Can someone help?
Dorian Chalom
2004-07-23 19:05:05 UTC
Permalink
The final table i need is this...
Post by Dorian Chalom
123 12 Algebra
123 23 Algebra Workbook
234 34 The Earth
345
345
"Rick Bean" <***@unrealmelange-inc.com> wrote in message news:***@tk2msftngp13.phx.gbl...
Dorian,
Since everything appears to be in the Books table, why not just use it or
use an SQL Select to just get the fields you want. e.g.
SELECT coursePK, bookPK, bookTitle ;
From Books ;
Into Table Final

Rick
Post by Dorian Chalom
OK I need this query designed....
Students
1234 Joe Blow
Courses
123 Math
234 Science
345 English
Books
1234 123 12 Algebra
1234 123 23 Algebra Workbook
1234 234 34 The Earth
345 45 Reading
345 56 Writing
TimeTable
1234 123
1234 234
1234 345
123 12 Algebra
123 23 Algebra Workbook
234 34 The Earth
345 45 Reading
345 56 Writing
Can someone help?
Cindy Winegarden
2004-07-25 18:32:08 UTC
Permalink
Post by Dorian Chalom
123 12 Algebra
123 23 Algebra Workbook
234 34 The Earth
345
345
123 12 Algebra
123 23 Algebra Workbook
234 34 The Earth
345 45 Reading
345 56 Writing
But my problem is some classes may have two books required for them
and may not yet be assigned to the student so how do I get the query
to include the two books requeried for the class?
Hi Dorian,

You are still not making sense. You've described your final data in two
different ways. You haven't described how the students table figures into
the mix at all either.

Here's my best guess at your data. Please post similar DDL (Data Definition
Language) that illustrates all the data needed for your query and when you
post the result you want, please post the names of the columns.

Create Cursor StudTTbl (IDStudent I, StudentName C(10))
Insert Into StudTTbl Values (1234, "Joe Blow")

Create Cursor CrsDemo (IDCourse I, CrsName C(10))
Insert Into CrsDemo Values (123, "Math")
Insert Into CrsDemo Values (234, "Science")
Insert Into CrsDemo Values (345, "English")

Create Cursor dbfCrsBk(IDStudent I NULL, ;
IDCourse I, BookNum I, Descript C(10))
Insert Into dbfCrsBk Values ;
(1234, 123, 12, "Algebra")
Insert Into dbfCrsBk Values ;
(1234, 123, 23, "Alg Workbk")
Insert Into dbfCrsBk Values ;
(1234, 234, 34, "The Earth")
Insert Into dbfCrsBk (IDCourse, BookNum, ;
Descript) Values (345, 45, "Reading")
Insert Into dbfCrsBk (IDCourse, BookNum, ;
Descript) Values (345, 56, "Writing")

Select ;
CrsDemo.IDCourse, ;
dbfCrsBk.BookNum, ;
dbfCrsBk.Descript ;
From ;
crsDemo ;
Left Join dbfCrsBk On ;
CrsDemo.IDCourse = dbfCrsBk.IDCourse
--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
***@mvps.org www.cindywinegarden.com
Loading...