Q: how to do the query “select * from tbl1 where tbl1.fname like ‘John’ and lname not in (select lname from tbl1 where ybirth>1975 and city=’London’)” without the subquery?

Kevin Kline says: This is a very simple SQL problem. The following query can be rewritten:

Select *
From tbl1
WHERE TBL1.fname LIKE ‘John’
AND lname NOT IN (SELECT lname
FROM tbl1
WHERE ybirth>1975 AND city=’London’)

As:

Select*
FROM tbl1
WHERE tble1.fname=’JOHN’
AND (ybirth <> 1975
AND city <> ‘LONDON’)

Note that you don’t need the LIKE operator since you didn’t use a wildcard operator in the name ‘JOHN’. Also note that ybirth must not be a datetime datatype or else the query would need to say”…AND ybirth <> ‘JAN-01-1975…’ and otherwise follow a proper datetime format.

Technorati Tags:
SQL, T-SQL

Add to: | Technorati | Digg | del.icio.us | Yahoo | reddit |