ckroon
Posts: 869
|
| Posted: 03/26/2008, 10:25 PM |
|
For simplicities sake let's say I have two tables:
Tests: 'testid', 'studentid'
Scores: 'scoreid', 'score_testid', 'score'
Now what I need to do is make a page that shows the user which tests do not have scores yet.
I have never run into this before, so excuse the newbness :)
I know ther is a magic Mysql function out there but I can't seem to find it. namely becase I have no idea what to search for...
Thanks!
_________________
Walter Kempees...you are dearly missed. |
 |
 |
wkempees
Posts: 1679
|
| Posted: 03/27/2008, 4:46 AM |
|
select * from test
left join scores on score_testid = testid
where not score_testid or score_testid is null
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
aondecker
Posts: 58
|
| Posted: 03/27/2008, 4:48 AM |
|
Quote ckroon:
For simplicities sake let's say I have two tables:
Tests: 'testid', 'studentid'
Scores: 'scoreid', 'score_testid', 'score'
Now what I need to do is make a page that shows the user which tests do not have scores yet.
I have never run into this before, so excuse the newbness :)
I know ther is a magic Mysql function out there but I can't seem to find it. namely becase I have no idea what to search for...
Thanks!
A left join will return both tables even if the rows in the table you are joining to are blank.
select studentid from tests left join scores on testid = score_testid .
That should return all rows of students that took the test and students that did not.
You can add a where on the end to filter out the ones that were already taken
EDIT*
Walter beat me by 2 minutes 
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 03/27/2008, 6:43 AM |
|
sorry 
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
ckroon
Posts: 869
|
| Posted: 03/27/2008, 4:51 PM |
|
Ok. So much for keeping it simple.
A friend got me this SQL which I applied to a simple table join and it worked.
SELECT *
FROM `vhs_assignments_master`
WHERE `assign_masterid` NOT
IN (SELECT `transcript_assignid`
FROM `vhs_atranscripts_master`
)
It showed me the records that were in the assignments table but NOT in the transcripts table.
Sooo... I went to my complicated page that uses the same two tables (along with some others for minor label displays) and added the Where statement from above to the SQL.
It throws no errors but the grid remains empty. Below is the SQL in full.. hope someone can show me the error. You can see the where statement at the end.
SELECT vhs_atranscripts_master.*, vhs_assignments_master.*, vhs_sections_master.*, course_name, FullName, last_name, first_name
FROM ((((vhs_sections_master RIGHT JOIN vhs_assignments_master ON
vhs_assignments_master.assign_sectionid = vhs_sections_master.sectionid) LEFT JOIN vhs_course_master ON
vhs_sections_master.section_courseid = vhs_course_master.courseid) LEFT JOIN userinfo ON
vhs_sections_master.section_teacherid = userinfo.TeacherId) LEFT JOIN vhs_atranscripts_master ON
vhs_atranscripts_master.transcript_assignid <> vhs_assignments_master.assign_masterid) LEFT JOIN classlist ON
vhs_assignments_master.assign_studentid = classlist.student_id
WHERE vhs_atranscripts_master.transcript_school_year = '{y}'
AND classlist.teacher_id <> {Expr0}
AND vhs_sections_master.section_term = {s_section_term}
AND vhs_sections_master.section_teacherid = {s_teacher_id}
AND vhs_course_master.short_course_name = '{s_course_name}'
AND assign_masterid NOT IN (SELECT transcript_assignid FROM vhs_atranscripts_master)
ORDER BY assign_sectionid
_________________
Walter Kempees...you are dearly missed. |
|
|
|
wkempees
Posts: 1679
|
| Posted: 03/27/2008, 5:14 PM |
|
if this works
SELECT vhs_atranscripts_master.*, vhs_assignments_master.*, vhs_sections_master.*, course_name, FullName, last_name, first_name
FROM ((((vhs_sections_master RIGHT JOIN vhs_assignments_master ON
vhs_assignments_master.assign_sectionid = vhs_sections_master.sectionid) LEFT JOIN vhs_course_master ON
vhs_sections_master.section_courseid = vhs_course_master.courseid) LEFT JOIN userinfo ON
vhs_sections_master.section_teacherid = userinfo.TeacherId) LEFT JOIN vhs_atranscripts_master ON
vhs_atranscripts_master.transcript_assignid <> vhs_assignments_master.assign_masterid) LEFT JOIN classlist ON
vhs_assignments_master.assign_studentid = classlist.student_id
WHERE vhs_atranscripts_master.transcript_school_year = '{y}'
AND classlist.teacher_id <> {Expr0}
AND vhs_sections_master.section_term = {s_section_term}
AND vhs_sections_master.section_teacherid = {s_teacher_id}
AND vhs_course_master.short_course_name = '{s_course_name}'
ORDER BY assign_sectionid
meaning it gives result > zero records
Then you may have to rewrite your WHERE like this:
WHERE
(
vhs_atranscripts_master.transcript_school_year = '{y}'
AND classlist.teacher_id <> {Expr0}
AND vhs_sections_master.section_term = {s_section_term}
AND vhs_sections_master.section_teacherid = {s_teacher_id}
AND vhs_course_master.short_course_name = '{s_course_name}'
)
AND assign_masterid NOT IN (SELECT transcript_assignid FROM vhs_atranscripts_master)
ORDER BY assign_sectionid
just guessing at this stage
W.
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
DonB
|
| Posted: 03/27/2008, 6:54 PM |
|
The grid is empty because the CountSQL doesn't build right when you have the
subselect (Seems like CCS can't figure out which SELECT to use when it
constructs the 'COUNT(*) FROM'). The query it constructs is invalid and you
get no count. Set your DataSource property Debug=true to see the bad query
it creates.
So, I have resorted to adding AfterSelect event code that does this
$rowcount = CCDLookUp("count(*) FROM (SELECT " . $radius . $where . "GROUP
BY Lat, Lon " . "HAVING distance <= $miles) as radius","","",$db);
$Container->DataSource->RecordsCount = $rowcount;
I do the work that CountSQL SHOULD perform automatically, obtaining the
actual number of rows and forcing it into the DataSource.
--
DonB
"ckroon" <ckroon@forum.codecharge> wrote in message
news:547ec32e5e5765@news.codecharge.com...
> Ok. So much for keeping it simple.
> A friend got me this SQL which I applied to a simple table join and it
> worked.
>
> SELECT *
> FROM `vhs_assignments_master`
> WHERE `assign_masterid` NOT
> IN (SELECT `transcript_assignid`
> FROM `vhs_atranscripts_master`
> )
>
> It showed me the records that were in the assignments table but NOT in the
> transcripts table.
>
> Sooo... I went to my complicated page that uses the same two tables (along
> with
> some others for minor label displays) and added the Where statement from
> above
> to the SQL.
> It throws no errors but the grid remains empty. Below is the SQL in full..
> hope
> someone can show me the error. You can see the where statement at the end.
>
> SELECT vhs_atranscripts_master.*, vhs_assignments_master.*,
> vhs_sections_master.*, course_name, FullName, last_name, first_name
> FROM ((((vhs_sections_master RIGHT JOIN vhs_assignments_master ON
> vhs_assignments_master.assign_sectionid = vhs_sections_master.sectionid)
> LEFT
> JOIN vhs_course_master ON
> vhs_sections_master.section_courseid = vhs_course_master.courseid) LEFT
> JOIN
> userinfo ON
> vhs_sections_master.section_teacherid = userinfo.TeacherId) LEFT JOIN
> vhs_atranscripts_master ON
> vhs_atranscripts_master.transcript_assignid <>
> vhs_assignments_master.assign_masterid) LEFT JOIN classlist ON
> vhs_assignments_master.assign_studentid = classlist.student_id
> WHERE vhs_atranscripts_master.transcript_school_year = '{y}'
> AND classlist.teacher_id <> {Expr0}
> AND vhs_sections_master.section_term = {s_section_term}
> AND vhs_sections_master.section_teacherid = {s_teacher_id}
> AND vhs_course_master.short_course_name = '{s_course_name}'
> AND assign_masterid NOT IN (SELECT transcript_assignid FROM
> vhs_atranscripts_master)
> ORDER BY assign_sectionid
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.yessoftware.com/
>
|
|
|
|
ckroon
Posts: 869
|
| Posted: 03/27/2008, 7:52 PM |
|
Walter...tried that.. no go.. will keep playing with it.
WIll look at Don B's example as well.
_________________
Walter Kempees...you are dearly missed. |
|
|
|
ckroon
Posts: 869
|
| Posted: 03/28/2008, 1:34 PM |
|
Got it!!!!
It was as simple as using the query builder properly....
Use the query builder as you normally wold, be sure to left join the two id columns that need to be matched up for comparison. Then make a Where statement that is an expression: field1id IS NULL.
That's it!
I will paste my complex sql down below in case it will help someone.
SELECT FullName, vhs_assignments_master.*, vhs_atranscripts_master.*, vhs_sections_master.*, last_name, first_name, grade, course_name
FROM ((((vhs_assignments_master LEFT JOIN vhs_atranscripts_master ON
vhs_assignments_master.assign_masterid = vhs_atranscripts_master.transcript_assignid) LEFT JOIN classlist ON
vhs_assignments_master.assign_studentid = classlist.student_id) LEFT JOIN vhs_sections_master ON
vhs_assignments_master.assign_sectionid = vhs_sections_master.sectionid) LEFT JOIN userinfo ON
vhs_sections_master.section_teacherid = userinfo.TeacherId) LEFT JOIN vhs_course_master ON
vhs_sections_master.section_courseid = vhs_course_master.courseid
WHERE ( transcript_assignid IS NULL )
AND classlist.last_name LIKE '%{s_last_name}%'
AND classlist.grade LIKE '%{s_grade}%'
AND userinfo.FullName LIKE '%{s_FullName}%'
AND vhs_course_master.course_name LIKE '%{s_course_name}%'
AND vhs_sections_master.section_term = {s_section_term}
ORDER BY section_courseid, assign_studentid
Ahhh.. CCS just saved me a few hours of writing Sql code :)
_________________
Walter Kempees...you are dearly missed. |
|
|
|
wkempees
Posts: 1679
|
| Posted: 03/28/2008, 3:35 PM |
|
score_testid is null
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
|
