Mysql4: SQL for selecting one or zero record

Answer 1

Try this:

SELECT to1.*
FROM t_order AS to1
WHERE
    to1.date IS NULL AND 
    to1.custId NOT IN (
     SELECT to2.custId
     FROM t_order AS to2
     WHERE to2.date IS NOT NULL
     GROUP BY to2.custId
    )
GROUP BY to1.custId

For MySQL 4:

SELECT to1.*
FROM t_order AS to1
LEFT JOIN t_order AS to2 ON
    to2.custId = to1.custId AND
    to2.date IS NOT NULL
WHERE
    to1.date IS NULL AND 
    to2.id IS NULL
GROUP BY to1.custId

Answer 2

For a specific customer it's:

SELECT *
FROM t_order
WHERE date IS NULL AND custId=? LIMIT 1

For all customers its:

SELECT a.*
FROM t_order a 
    LEFT JOIN t_order b ON a.custId=b.custID and a.id != b.id
WHERE a.date IS NULL AND b.date IS NULL
GROUP BY custId;

Answer 3

This query will use one pass over index on custId.

For each distinct custId it will use one subquery over same index.

No GROUP BY, no TEMPORARY and no FILESORT — efficient, if your table is large.

SELECT VERSION()

--------
'4.1.22-standard'

CREATE INDEX ix_order_cust_id ON t_order(custId)

SELECT id, custId, order_date
FROM (
  SELECT o.*,
    CASE
      WHEN custId <> @c THEN
        (
        SELECT 1
        FROM   t_order oi
        WHERE  oi.custId = o.custId
          AND  order_date IS NOT NULL
        LIMIT 1
        )
    END AS n,
    @c <> custId AS f,
    @c := custId
  FROM
    (
    SELECT @c := -1
    ) r,
    t_order o
  ORDER BY custId
) oo
WHERE n IS NULL AND f

---------
1, 10, ''
5, 13, ''

Answer 4

First filter out rows with dates, then filter out any row that has a similar row with a lower id. This should work because the matching record with the least id is unique if id is unique.

select * from t_order o1
    where date is null
      and not exists (select * from t_order o2
                         where o2.date is null
                           and o1.custId = o2.custId
                           and o1.id > o2.id)