sql query help multiple field values

me_mcneely
me_mcneely used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
try this
select * from table1
where qid in ('m0250', 'm0440') and val = '1'

Author

Commented:
No information pulled this time....
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
>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...
Expert Spotlight: Joe Anderson (DatabaseMX)

We’ve posted a new Expert Spotlight!  Joe Anderson (DatabaseMX) has been on Experts Exchange since 2006. Learn more about this database architect, guitar aficionado, and Microsoft MVP.

Author

Commented:
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?

Author

Commented:
I only want the docsys numbers that have the two qid values with a val value of 1....
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
ok, I think this should be what you are looking for:

select t1.docsys
from table1 t1
join table1 t2
  on t1.docsys = t2.docsys
 and t1.qid = 'm0250'
 and t2.qid = 'm0440'
 and t1.val  like '1 -%'
 and t2.val  like '1 -%'

Author

Commented:
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!!
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
>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...

Author

Commented:
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 -%'

Author

Commented:
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!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial