Query of history question 1

[skiflyer]

Ok, I believe I can boil this down to one table to ask my question... Say I have the below data set, we'll call the first column letters, the second numbers. I want to return all the letters which have a number entry which is 1 and a number entry which is 5... can I do this without using temporary tables? I'm almost sure I can, but am totally stumped at the moment.

A 1
A 2
B 4
B 5
C 1
C 5

Thanks,
Rob

 

[sleipnir214]

Do you mean "the set of all numbers where the value of the number column is either 1 or 5"?

Then:

SELECT * from tblname
WHERE numbers = 1 or numbers = 5

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[skiflyer]

No, I meant where it has been both 1 and 5... here's what I got... what I'm really doing is looking through a history table, and finding items which have been in two different states within a given time range. Anyway, I think I've solved it... for anyone who may come later

SELECT tbl1.letter
FROM tblname AS tbl1
JOIN tblname AS tbl2 ON tbl1.letter=tbl2.letter
WHERE tbl1.number=1
AND tbl2.number=5

But, if anyone sees a problem with this, please let me know.

-Rob

 

[r937]

that's good for two conditions

for three or more, it gets rather awkward

this works too:

[tt]select letter
from tblname
where number = 1
or number = 5
group
by letter
having count(*) = 2[/tt]

the nice part about this you can easily see how to extend it to more conditions

rudy

http://r937.com/

 

[skiflyer]

Granted... however, if a letter has visited 1 twice, it will be a false positive.

-Rob

 

[skiflyer]

Sorry for the double reply... and if it has visited 1 twice, and 5 once, it will be a false negative.

If it's a unidirectional non-repeating graph (I forget the proper acronym) that would work fine, unfortunately, I don't have that luxury.

-Rob

 

[r937]

oh ye of little faith

just change it to

... having count(distinct number) = 2


rudy