me_mcneely
asked on
sql query help multiple field values
Hi --
This will be easy for alot of you out there...so please forgive my ignorance and don't laugh :o) ....I am using SQL 2000....what is the best way to query data when you have two fields in the same table with multiple values but only need specific information ? For example, I only need the qid values that equal m0440 and m0250 and their val values that = 1. I tried the following (but I get more data than needed): I need the two qid values that have the val 1 response -- both answers are a little different but start out the same.
select * from table1
where qid in ('m0250', '11m0440') and val like '1 -%'
Thanks for your help!
memcneely
This will be easy for alot of you out there...so please forgive my ignorance and don't laugh :o) ....I am using SQL 2000....what is the best way to query data when you have two fields in the same table with multiple values but only need specific information ? For example, I only need the qid values that equal m0440 and m0250 and their val values that = 1. I tried the following (but I get more data than needed): I need the two qid values that have the val 1 response -- both answers are a little different but start out the same.
select * from table1
where qid in ('m0250', '11m0440') and val like '1 -%'
Thanks for your help!
memcneely
ASKER
No information pulled this time....
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
>qid in ('m0250', '11m0440')
does that mean you have 2 rows, one with qid = m0250, and another one with llm0440?
in that case, what other field is to link the 2 rows...
does that mean you have 2 rows, one with qid = m0250, and another one with llm0440?
in that case, what other field is to link the 2 rows...
ASKER
yes...what I need and please bear with me as i explain so inarticulately are the the docs that have qid values that equal ('m0250', '11m0440') and both have a val value of 1...I want to pull all of the docsys numbers (which is the first field in the table) that have both qid values and val of 1...make sense?
ASKER
I only want the docsys numbers that have the two qid values with a val value of 1....
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am not sure that I understand this especially with the join ...table1 would be replaced by the actual table name (docassrsp)? Thank you for your help and patience!!
>table1 would be replaced by the actual table name (docassrsp)?
yes. t1 and t2 are aliases which you give to the table, so you can self-join the table.
think of this like you created 2 views (v1 and v2) of the same table, one with the filter qid = m0250, and the other with qid = m0440...
yes. t1 and t2 are aliases which you give to the table, so you can self-join the table.
think of this like you created 2 views (v1 and v2) of the same table, one with the filter qid = m0250, and the other with qid = m0440...
ASKER
I ran the query but I am not getting any rows returned......I know that there are docsys nums with both qid and val values.....what have I done wrong?...thanks again
select t1.docsys
from docassrsp t1
join docassrsp t2
on t1.docsys = t2.docsys
and t1.qid = 'm0250'
and t2.qid = 'm0440'
and t1.val like '1 -%'
and t2.val like '1 -%'
select t1.docsys
from docassrsp t1
join docassrsp t2
on t1.docsys = t2.docsys
and t1.qid = 'm0250'
and t2.qid = 'm0440'
and t1.val like '1 -%'
and t2.val like '1 -%'
ASKER
okay.....ignore the previous comment, I forgot to update my second qid value to the actual name (11m0440)...can't find what's not there!!
select * from table1
where qid in ('m0250', 'm0440') and val = '1'