MySQL Syntax Statement

[Cerebrus' Maw]

Hi guys. Just struggling on wrapping my head around a MySQL statement I need for unique records.

I have a table that will be queried by a date range. IE-> return records between 2011-07-20 and 2011-07-31. Now imagine these records have a field called _ID

What complicates it is that it must ignore records that have a same _ID outside of the date range.

EG. Running a query with the above dates, and it returns 10 records with _ID 1-10. But three of these records, lets say 6,7,8 occur before the date range. The query has to ignore these records with _ID 6,7,8.

The PHP programmer in me see's the easy solution of just running two queries and looping on an exclude basis, but this table will get rather big fast, so would prefer as little mysql overhead as possible....

 

[bpaluzzi]

Hi guys. Just struggling on wrapping my head around a MySQL statement I need for unique records.

I have a table that will be queried by a date range. IE-> return records between 2011-07-20 and 2011-07-31. Now imagine these records have a field called _ID

What complicates it is that it must ignore records that have a same _ID outside of the date range.

EG. Running a query with the above dates, and it returns 10 records with _ID 1-10. But three of these records, lets say 6,7,8 occur before the date range. The query has to ignore these records with _ID 6,7,8.

The PHP programmer in me see's the easy solution of just running two queries and looping on an exclude basis, but this table will get rather big fast, so would prefer as little mysql overhead as possible....

Not quite following it -- can you describe your schema, and what you're trying to do in a little more detail?

Cheers,
b

 

[Cerebrus' Maw]

Not quite following it -- can you describe your schema, and what you're trying to do in a little more detail?

Cheers,
b

No worries. A table with some data.

Date | _ID | Name |
===================
2011-07-05 | 1 | Alan |
2011-07-09 | 6 | Paul |
2011-07-10 | 2 | Steve |
2011-07-18 | 9 | James|
2011-07-19 | 6 | Rick |
2011-07-20 | 5 | Peter |

Now, I do a search for records between 2011-07-18 to 2011-07-20. It should only return James and Peter, as Rick has an _ID of 6, which exists earlier in the table, even though my date range does not include this.

Does that clear it up? Just to let you know, I did manage to figure it out, but I'll let it up as a challenge As a bonus it should also exclude if the _ID exists after the date rage specified. So if a record as such:

2011-07-25 | 9 | Henry

then only Peter should return, as James and Henry have the same _ID (9)

 

[Thom_Edwards]

SELECT * FROM yourTable
WHERE Date BETWEEN 2011-07-18 AND 2011-07-20
AND _ID NOT IN (SELECT _ID FROM yourTable
WHERE Date NOT BETWEEN 2011-07-18 AND 2011-07-20)

This is semi-pseudocode, meaning the syntax is not spot on, but the spirit of it should get you there.

Now, what do I win? ;-)

 

[Cerebrus' Maw]

Ding Ding, Winner!

Pretty much exactly how I did, although I did tool around with EXISTS and NOT EXISTS before settling on NOT IN. I've got a feeling that I might be coming back to this thread, as the project I'm working on has MySQL optimization as a priority, and I aint that much of a gun on SQL languages.

Prize? Uh....I must have.....left it outside.... in my car......Back in a jiffy!