SQL Inner join with limit on subquery not to affect query

Question

I'm running a query in MySQL with an INNER JOIN that has a LIMIT on the subquery

The problem is, that the LIMIT on the subquery is affecting the number of rows returned.

I want to select all rows from table 1 (tickets) where the last row in ticket_updates relevant (t.ticketnumber = tu.ticketnumber) was not numeric in column contact_name

SELECT t.* 
  FROM tickets t
  JOIN
     ( SELECT ticketnumber 
         FROM ticket_updates 
        WHERE type = 'update' 
          AND concat('', contact_name * 1) <> contact_name 
        ORDER 
           BY sequence DESC 
        LIMIT 1
     ) tu
    ON t.ticketnumber = tu.ticketnumber
 WHERE t.status <> 'Completed' 
   AND LOWER(t.department) = 'support';

But the results shown just return the 1 row

There are multiple rows in ticket_updates that relate to each row in tickets based on tickets.ticketnumber =ticket_updates.ticketnumber`

the contact_name column can either be a string or integer. I picked up the concat('', contact_name * 1) <> contact_name from another SO Post which tells me whether the value is numeric or not.

So I want to pick up the latest row (ORDER BY sequence DESC) in ticket_updates for each row in tickets and see whether contact_name is not numeric

Have you tried using `SELECT DISTINCT` and remove the `LIMIT 1` for the subquery? — Kaddath, Mar 04 '19 at 10:34
Are you sure that you're using a case sensitive collation? And concat('', contact_name * 1) <> contact_name? What? That can't be good. — Strawberry, Mar 04 '19 at 10:37
Your query does not look wrong at first glance, so if it returns only one row, its because you have only one ticket with status completed for the support department. If not, show your data. — Thomas G, Mar 04 '19 at 10:45
You can also use `max` instead of `limit`, but it will depends on rows inside your table. — jned29, Mar 04 '19 at 10:47

Kaddath · Answer 1 · 2019-03-04T14:38:17.573

-1

Your query selects one row because (as you concluded yourself) your subquery is limited to one result.

what you want is probably something similar to what can be found in this answer (I assumed that you want the entry with biggest value for sequence, if the contratry, change to MIN)

with a subquery (there should be an adaptation from the shortest more optimized query in the cited answer, but let's see if this one works first):

SELECT t.*
FROM tickets t
INNER JOIN ( SELECT ticketnumber, MAX(tu.sequence) AS maxSequence
        FROM ticket_updates tu
        WHERE tu.type = 'update' AND concat('', tu.contact_name * 1) <> tu.contact_name
        GROUP BY ticketnumber ) tu2
    ON (t.ticketnumber = tu2.ticketnumber)
WHERE t.status <> 'Completed' 
AND LOWER(t.department) = 'support';

see it in action

edited Mar 04 '19 at 14:38

answered Mar 04 '19 at 11:09

Kaddath

5,933
1
9
23

1

serial voters that don't leave any comments are funny.. let your frustration go out, if i can be any help for this at least ;) – Kaddath Mar 04 '19 at 11:20
aha, I see what you've done. I've just realised the subquery isn't going to work, I need to remove the `concat('', tu.contact_name * 1) <> tu.contact_name` clause and get the row with the maximum sequence and then check whether the `contac_name` column is numeric or not – charlie Mar 05 '19 at 12:32
any ideas on how I can achieve that? – charlie Mar 11 '19 at 12:43
well actually the `concat` on `contact_name` seems to work for testing if numeric, it does in the exemple linked – Kaddath Mar 11 '19 at 12:55

SQL Inner join with limit on subquery not to affect query

1 Answers1