Remove WHERE condition when subquery does not return any row

I'm writing a complex MySQL query and I'm having trouble figuring out how to finish it.

Here's the part that's giving me trouble (it's only a part of my query):

SELECT * FROM table AS t1
WHERE date < (
    SELECT date FROM table AS t2 
    WHERE phase="B" AND t2.target = t1.target
)

Basically, I have items, each one with a date, a phase (A,B,C) and a target. For a target, there are several items of type A, then an single and optional item of type B, then items with type C.

For each target, I want to select all the row开发者_StackOverflow中文版s following these conditions:

1. If there is an item with phase "B" (lets call him itemX), I want to return all items with a date inferior to the date of itemX
2. If there is no item with phase "B", I want to return all rows

The date parameter is very important. In most cases, the 3 phases are distinct, and cannot overlap, but there are some cases in which that happens.

The problem here, is that my subquery does not return any rows in case 1, and a single cell in case 2.

If we are in case 1, the whole condition WHERE date < (...) is irrelevant and should not be applied in the query.

I tried several possibilities with IFNULL and EXISTS, but I think I did it wrong because I keep getting syntax errors.

---

SELECT  m.*
FROM    (
        SELECT  target, MAX(date) AS maxdate
        FROM    mytable
        ) md
JOIN    mytable m
ON      m.target = md.target
        AND m.date <
        COALESCE
        (
        (
        SELECT  date
        FROM    mytable mb
        WHERE   mb.target = md.target
                AND mb.phase = 'B'
        ORDER BY
                mb.target, pmb.phase, mb.date
        LIMIT 1
        ),
        maxdate + INTERVAL 1 SECOND
        )

Create two indexes:

mytable (target, date)
mytable (target, phase, date)

for this to work fast.

---

Perhaps

SELECT *
FROM table AS t1
LEFT JOIN table AS t2 ON t2.target = t1.target AND (t1.date < t2.date)
WHERE (phase = 'B')

I'm assuming the table in your query is actually two tables and you're not doing a self join? If so, then you'll have to specify which table's phase you're referring to.

---

You might try

SELECT * FROM table AS t1
left join 
table as t2 
on t1.Target = t2.Target
and t2.phase="B"
where t2.target is null OR 
OR t1.date < t2.Date

---

The code you posted is called "One subquery per condition anti-pattern". Use the CASE-WHEN-THEN.

---

SELECT t1.*
  FROM table t1
  LEFT
  JOIN ( SELECT t.target
              , MIN(t.date) AS b_date
           FROM table t
          WHERE t.phase = 'B'
          GROUP BY t.target
       ) t2
    ON t1.target = t2.target AND t1.date < t2.b_date

If there is some guarantee that a given target will have no more than one row with "phase"='B' at most, you can get by without the MIN and GROUP BY, like this:

SELECT t1.*
  FROM table t1
  LEFT
  JOIN ( SELECT t.target
              , t.date AS b_date
           FROM table t
          WHERE t.phase = 'B'
       ) t2
    ON t1.target = t2.target AND t1.date < t2.b_date