How would I write this? 2

[VAMick]

I have a page that I output a list of courses from the database.
I'd like to filter off courses that have an entry in another table, but not sure how to write that.
Here is the sql statement i have now

SELECT corid, course_num, ctitle FROM spec_course ORDER BY course_num ASC

and I want it to look in the spec_coursecats table to see if there is an entry for the corid that is in the spec_course table and if it finds one, to exclude that course from being output.

how would that be written?

 

[TonyGroves]

How about:
[tt]
SELECT c.corid, c.course_num, c.ctitle
FROM
spec_course c
LEFT JOIN spec_coursecats cc ON c.corid=cc.corid
WHERE cc.corid IS NULL
ORDER BY course_num ASC
[/tt]

 

[VAMick]

Hmmm, that doesn't seem to work.

 

[VAMick]

I'm thinking it has something to do with the IS NULL.
See, there wouldn't be an entry in the spec_coursecats table for corid for the courses that i want to appear from the spec_course table.

Maybe I'm just really confused.

 

[TonyGroves]

How does it not work?

The query I gave will join each record of spec_course to spec_coursecats; where there is no matching entry in spec_coursecats, the spec_coursecats fields will be null. Therefore those joined records where cc.corid is null are the records for which there is no entry in spec_coursecats.

 

[VAMick]

I'm not sure why your example didn't work, Here is what did work:

SELECT c.corid, c.course_num, IF(LENGTH(c.ctitle)>70, CONCAT(LEFT(c.ctitle,70),'...'),c.ctitle) AS trimctitle, COUNT(cc.corid) AS numcorid FROM spec_course c LEFT JOIN spec_coursecats cc ON cc.corid=c.corid GROUP BY corid HAVING numcorid=0 ORDER BY c.supid, c.course_num ASC

 

[r937]

that should not have worked because the GROUP BY is ambiguous, and further, in a grouped query you cannot ORDER BY a column that isn't in the SELECT

besides, you don't need to group

try this --

select c.corid
     , c.course_num
     , if(length(c.ctitle)>70
       , concat(left(c.ctitle,70),'...')
       , c.ctitle) as trimctitle
  from spec_course c  
left outer
  join spec_coursecats cc 
    on c.corid = cc.corid 
 where cc.corid is null
order 
    by c.supid
     , c.course_num

r937.com | rudy.ca

 

[TonyGroves]

What's this, Rudy using IF( ... ) ?!

 

[r937]

yeah, i was gonna correct that part too, dunno why i decided to leave it



r937.com | rudy.ca

 

[VAMick]

Are you saying the IF is inappropriate too? If so, how come?

 

[r937]

no, not inappropriate, for mysql it is fine

some feel a CASE expression instead of the IF function is better, because it is standard sql, which mysql supports, as do all other databases (while very few support IF), which means that if ever some day you find yourself writing sql for a different database, you won't stumble



r937.com | rudy.ca

 

[VAMick]

Can I see an example of using the CASE expression in my sql statement so that I know what you mean (for the future)?

 

[r937]

select c.corid
     , c.course_num
     , case when length(c.ctitle)>70
        then concat(left(c.ctitle,70),'...')
        else c.ctitle) end   as trimctitle
  from spec_course c  
left outer
  join spec_coursecats cc 
    on c.corid = cc.corid 
 where cc.corid is null
order 
    by c.supid
     , c.course_num

r937.com | rudy.ca

 

[VAMick]

oh, it's very similar. Well, I'll give it a try. Good to know new things.

thanks guys.