[thelist] Re: SQL gurus

Sean German ethanol at mathlab.sunysb.edu
Fri Mar 25 15:26:10 CST 2005


> Ok just after I sent this out I had a revelation, doesn't 
> that always seem to happen, doh!
> 
> LEFT JOIN is my friend.
> 
> Turned the query into:
> 
> SELECT m1.date,m1.mileage,m1.notes,m2.date AS \'match\' FROM 
> m m1 LEFT JOIN m m2 ON (m1.date=m2.date AND 
> m1.mileage=m2.mileage AND m2.notes LIKE \'%'.$searchString.'%\')';

One possible issue with the query as the problem is presented.  If you have
two records with the same date, same mileage, and different notes, you may
get tripped up.  If one record has the search string in the notes and the
other doesn't, both will come back as matches.

Of course there are probably other restrictions on the data stripped out for
brevity's sake.  Anyway, here's another query that may be appropriate
depending on the data.

SELECT     date, mileage, notes, '' AS IsMatch
FROM         m
WHERE     (NOT (notes LIKE '%breaks%'))
UNION
SELECT     date, mileage, notes, 'match' AS IsMatch
FROM         m
WHERE     (notes LIKE '%breaks%')



tgif,



Sean G.




More information about the thelist mailing list