[SOLVED] Query Help

[jaymc]

I want to retreive 2 rows of data from 1 table but with different WHERE clause basically

 

Here is what a row may look like

 

 

id - user - time - valid

12 - Jamie - 12312312423 - 2

15 - Jamie - 12315898973 - 0

14 - Jamie - 12389759578 - 1

 

I want to have 1 query return all rows:

"WHERE user = Jamie AND valid = 1 LIMIT 0,1"

AND

"WHERE user = Jamie AND time > 25000 LIMIT 0,1"

 

This should give me

 

12 - Jamie - 20000 - 2

12 - Jamie - 30000 - 1

 

What is the best way to do this without pulling out all the DATA and using PHP to find the matches, aswell as using 2 queries

 

Any ideas?

[rajivgonsalves]

use the UNION in mysql

select * from ...
UNION 
select * from ...

 

hope its helpful

[jaymc]

Yeh thats what I have been playing with

 

Is this the most optimised approach to go about this or are there more known methods in MYSQL

[rajivgonsalves]

Actually seeing your code you can do it in one query

 

select * from ...
WHERE user = Jamie AND (valid = 1 OR time > 25000) LIMIT 2

 

not too sure it will work tho just a thought

[fenway]

Actually seeing your code you can do it in one query

 

select * from ...
WHERE user = Jamie AND (valid = 1 OR time > 25000) LIMIT 2

 

not too sure it will work tho just a thought

Yes, but depending on which version of mysql you're using and what indexes your have, this may or may not be more efficient.

[jaymc]

Oh of course, using OR!

 

I dont know why I didnt think of that!

 

I might benchmark both methods as I have just wrote the UNION way

 

Thanks guys

[jaymc]

Wait, I just remembered why I couldnt use OR

 

I actually need to find

 

a) the first row where valid = '1'

b) The highest number in the time field

[rajivgonsalves]

Yes I though of the same thing I think going with the union is better bet if you have two records with valid = '1' they both will show up instead of a combination..