CODEWITHSUNDEEP
mysqlsqlsubquery
3rgo
3rgo

Reputation: 3153

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 rows 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.

Upvotes: 0

Views: 240

Answers (5)

spencer7593
spencer7593

Reputation: 108480

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

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425683

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.

Upvotes: 1

cmsjr
cmsjr

Reputation: 59215

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

Upvotes: 1

Fergara
Fergara

Reputation: 947

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

Upvotes: 0

Marc B
Marc B

Reputation: 360772

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.

Upvotes: 1

Related Questions