[SQL] join/case

[jtx]

Hi everyone, I'm trying to do a left join on two tables, mainly because
data from table 'b' (lists) may or may not exist, and if it doesn't I
want results.  However, if data from table lists DOES exist, I want to
run a conditional on it, and then return data based on whether the
conditional is true or false.

Basically, I have something like this:

Select o.id,o.num_purch,o.program from orders o left join lists l on
l.order_id=o.id where o.uid=1 and o.status!='closed'

This query would return something like:

id | num_purch | program
+---+-
  1 |   100 |   1
  2 |   150 |   2


However, I want to throw an extra conditional in there that says if
l.status='processing', then don't return anything.  So, I tried:

Select o.id,o.num_purch,o.program from orders o left join lists l on
l.order_id=o.id and l.status!='processing' where o.uid=1 and
o.status!='closed'. 

Well, that doesn't work, it returns all the data anyway.  I'm guessing
it's because l.status!='processing' is part of the left join.  

The trick here is that, like I said, there may be NO data from the lists
table, and if not, return everything.  If there is data in lists that
has the order id in it, check to make sure l.status!='processing'. If it
does, don't return it, if it doesn't, return.

Thanks for your help, and sorry if I don't make much sense I tend to
ramble :)


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

[SQL] relevance

[jtx]

I'm trying to build a really basic search engine for a site I'm using -
say I'm going to simplify this as much as I can.

Say I have a table with 2 columns: id, message

Person wants to search for the term 'sql'.  So, I'd do a simple search
like:

select id from tablename where message like '%sql%';

If there any way to determine exactly how many times 'sql' is matched in
that search in each particular row, and then sort by the most matches,
or am I going to have to write a script to do the sorting for me?

Thanks!


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly