CODEWITHSUNDEEP
mysqlsqlleft-join
Nakrule
Nakrule

Reputation: 609

MySQL: Add a WHERE in a LEFT JOIN

I have the following table:

enter image description here

SequenceNumber are always a multiple of 10. I would like to get, for a specific cpId, the smallest free sequence number (still in a multiple of 10). For example, for cpId = 1, the smallest available should be 20. For cpId = 2, it should be 10.

I have the following statement to get the smallest available sequenceNumber for all cpId, and I don't know how I can add a WHERE cpId = x inside the statement:

SELECT MIN(t1.sequenceNumber + 10) AS nextID
FROM LogicalConnection t1
   LEFT JOIN LogicalConnection t2
       ON t1.sequenceNumber + 10 = t2.sequenceNumber
WHERE t2.sequenceNumber IS NULL;

DB fiddle: https://www.db-fiddle.com/f/ag67AkFzfwPZEva8bTN7Q3/2#&togetherjs=L9nHb3Uu7O

Thank you for your help!

Upvotes: 1

Views: 56

Answers (2)

Vinod Sherikar
Vinod Sherikar

Reputation: 9

You have to join both tables on cpId column and group the rows with similar cpId. Where can be used to filter rows. Below query gives you the cpId and their corresponding next available minimum sequence number.

SELECT t1.cpId, MIN(t1.sequenceNumber + 10) AS nextID
FROM LogicalConnection t1
LEFT JOIN LogicalConnection t2
       ON t1.sequenceNumber + 10 = t2.sequenceNumber
       and t1.cpId = t2.cpId
group by (t1.cpId)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You can use lead() to get the next number and then some simple logic:

select cpid,
       (case when min_sn > 10 then 10
             else min(sequenceNumber) + 10
        end)
from (select t.*,
             min(sequenceNumber) over (partition by cpid) as min_sn,
             lead(sequenceNumber) over (partition by cpid order by sequenceNumber) as next_sn
      from t
     ) t
where next_sn is null or next_sn <> sequenceNumber + 10
group by cpid, min_sn;

Upvotes: 1

Related Questions