-2

I don't know much about MySQL but I have to do sth. with this nice thing. :) I have 1 Table with 2 ID Fields in it (ID and REFID). I only want to select the ID's which are not between other ID to REFID.

Row 1: ID = 1 and REFID = 15

Row 2: ID = 17 and REFID = 20

Row 3: ID = 19 and REFID = 25

I only want Row 1 and 2 in my result. because Row 3 is crossing Row 2. Sorry for my bad explanation, I hope you know what I want to know. :)

Thorsten
  • 3
  • 3

3 Answers3

0

For this, you should use MySql procedures. You can find the tutorial here.

  • Whilst this may theoretically answer the question, [it would be preferable](//meta.stackoverflow.com/q/8259) to include the essential parts of the answer here, and provide the link for reference. – Barmar Nov 03 '17 at 21:22
0

Are you doing this with PHP?

If so maybe something like this... - create a query to return the desired columns of the table in an array ORDER by id - then create a loop to start with get the records starting with the id equal to the lowest id number - and repeat the loop until you have retrieved the record with the highest id number you desire.

  • or use WHERE in the query to specify the id range you want returned.

One of these approaches should get you there... I think :-)

Fran_3
  • 378
  • 1
  • 3
  • 12
  • Yeah, I'm using php, too. But I thought it would be easier and cleaner to do this in db. The problem is, that the Id Range changes every row. – Thorsten Nov 03 '17 at 21:21
0

This is based on the general pattern for finding a row in a table that doesn't match another table, as in Return row only if value doesn't exist. But in this case the two tables are the same table, and the match is by checking for id being between id and RefID in the other row.

SELECT t1.*
FROM yourTable AS t1
LEFT JOIN yourTable AS t2 ON t1.id > t2.id AND t1.id < t2.RefID
WHERE t2.id IS NULL

DEMO

Barmar
  • 741,623
  • 53
  • 500
  • 612