Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2003
    Posts
    35

    Unanswered: SQL: Conditional Query Question

    Hi all,

    I'm just learning SQL so if you could please bear with me.

    I'm looking to create a statement that will let me only show rows that have these two conditions:
    when (Sequence_number=1 AND answer='n')
    AND (Sequence_number=5 AND answer='y') for any given User_ID

    User_ID, Sequence_number and answer are columns in the table.

    Any help would be great. Thanks.

  2. #2
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    select * from your_table where (Sequence_number=1 AND answer='n')
    AND (Sequence_number=5 AND answer='y')




    just replace the 'your_table' with the actual table .

  3. #3
    Join Date
    Sep 2003
    Posts
    35
    Thanks Harshal,
    I'm getting "no rows selected" errors and I'm sure there should be output. Any ideas?
    Last edited by tkchung; 09-26-03 at 11:32.

  4. #4
    Join Date
    Jan 2003
    Location
    India
    Posts
    523

    Re: SQL: Conditional Query Question

    Originally posted by tkchung
    Hi all,

    I'm just learning SQL so if you could please bear with me.

    I'm looking to create a statement that will let me only show rows that have these two conditions:
    when (Sequence_number=1 AND answer='n')
    AND (Sequence_number=5 AND answer='y') for any given User_ID

    User_ID, Sequence_number and answer are columns in the table.

    Any help would be great. Thanks.

    r u sure u want the results satisying both the conditions when (Sequence_number=1 AND answer='n') AND (Sequence_number=5 AND answer='y')


    or one of them should be satisfied?

    select * from your_table where (Sequence_number=1 AND answer='n')
    OR (Sequence_number=5 AND answer='y')

  5. #5
    Join Date
    Sep 2003
    Posts
    35
    Yes. I'd like to know when question 1 is answered "no" AND when Question 5 is answered "yes" for a given user.

    So, in the table, the data would look like
    user_ID seq_no
    User1 1 N
    User1 5 Y
    User2 1 N
    User2 5 Y
    User3 1 N
    etc.

    I do not want to see anyone that had anything other than #1 n and #5 y. Hope that clarifies.

  6. #6
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    select * from your_table where (Sequence_number=1 AND answer='n')
    OR (Sequence_number=5 AND answer='y')

  7. #7
    Join Date
    Sep 2003
    Posts
    35
    Harshal. Thanks. This at least gives me input but more than I'm looking for.

    I'm getting all of the Q#1 "n" for instances where Q#5 is left blank. Is there another where condition that I need to add to give it only cases where q#1 and Q#5 both have answers?

    The above also gives me Q#1 "Y" and Q#5 "Y" but just doesn't show me the Q#1 "Y" for that given user. This is why I think I need an "AND" rather than an OR, but that seems to give me no results.

    I'm thinking I may need to use the user_ID ... say that if both Q1 and Q5 are present, then only show me those that have Q1=n and Q5=y

    Thanks for all your help
    Last edited by tkchung; 09-26-03 at 12:31.

  8. #8
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    Originally posted by tkchung
    Harshal. Thanks. This at least gives me input but more than I'm looking for.

    I'm getting all of the Q#1 "n" for instances where Q#5 is left blank. Is there another where condition that I need to add to give it only cases where q#1 and Q#5 both have answers?

    The above also gives me Q#1 "Y" and Q#5 "Y" but just doesn't show me the Q#1 "Y" for that given user. This is why I think I need an "AND" rather than an OR, but that seems to give me no results.

    Thanks for all your help
    select * from your_table where answer is not null and ( (Sequence_number=1 AND answer='n')
    OR (Sequence_number=5 AND answer='y') )

    ----------------
    or
    ------------
    select * from your_table where len(answer)>0 and ((Sequence_number=1 AND answer='n')
    OR (Sequence_number=5 AND answer='y'))

  9. #9
    Join Date
    Sep 2003
    Posts
    522
    select count(*) from (
    select q1.user_id, answer1 = q1.answer, answer5 = q2.answer
    from your_table q1
    inner join your_table q2
    on q1.user_id = q2.user_id
    and q1.sequence_number = 1
    and q2.sequence_number = 5
    ) x where user_id = 'Joe'
    and answer1 = 'n'
    and answer5 = 'y'

  10. #10
    Join Date
    Sep 2003
    Posts
    35
    MS_SQL_DB, Thanks a lot. Inner join was what I was looking for.

    I tried your solution but am getting "FROM not found where expected". I see your from statement, so I don't know what the problem is. Any ideas?

  11. #11
    Join Date
    Sep 2003
    Posts
    522
    the statement is working, i just tested it. make sure to replace your_table with your table name.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •