SQL question

[Jay]

Hi, I'm an sql noob and I would like to know if there is an easy way to
query data from this table
I have a table with ID,RANK, WORD as columns, id and rank are ints and word
is a text field. There are multiple entries for any number of id's.
ex.
1,5,word1
1,4.3,word2
1,2,word3
2,5,word1
2,4.3,word2
2,2,word3
etc.

What I want to get out of it is: Show me all ID's where there is both a row
that contains "word1" and a row that contains "word2".

Any pointers?
Thanks,
Jay

[Bill Karwin]

Jay wrote:

What I want to get out of it is: Show me all ID's where there is both a row
that contains "word1" and a row that contains "word2".

This is a slightly tricky way, that should work in either MySQL 3.x or 4.x:

SELECT DISTINCT T1.ID
FROM MyTable T1 INNER JOIN MyTable T2 ON T1.ID = T2.ID
WHERE T1.WORD LIKE '%word1%' AND T2.WORD LIKE '%word2%';

Regards,
Bill K.

[news]

"Bill Karwin" <bi**@karwin.com> wrote in message
news:cb********@enews2.newsguy.com...

Jay wrote:

What I want to get out of it is: Show me all ID's where there is both a row that contains "word1" and a row that contains "word2".
This is a slightly tricky way, that should work in either MySQL 3.x or

4.x:
SELECT DISTINCT T1.ID
FROM MyTable T1 INNER JOIN MyTable T2 ON T1.ID = T2.ID
WHERE T1.WORD LIKE '%word1%' AND T2.WORD LIKE '%word2%';

Regards,
Bill K.

Thanks Bill, it doesn;t seem to work. I get a result for some queries but
not for others.

[Bill Karwin]

news wrote:

Thanks Bill, it doesn;t seem to work. I get a result for some queries but
not for others.

It worked for me with the sample data you provided in your first post.
If there are other details about your data that you're not describing,
then there's not much I can suggest.

Best of luck!
Bill K.

[Jay]

"Bill Karwin" <bi**@karwin.com> wrote in message
news:cb*********@enews1.newsguy.com...

news wrote:

Thanks Bill, it doesn;t seem to work. I get a result for some queries but not for others.

It worked for me with the sample data you provided in your first post.
If there are other details about your data that you're not describing,
then there's not much I can suggest.

Best of luck!
Bill K.

Bill,

Nope, you are right, it does work. I had 2 tables that were slightly
different and I ran it against the wrong one.

Many thanks,
Jay