Results 1 to 9 of 9

Thread: SQL query

  1. #1
    Join Date
    Jan 2010
    Posts
    64

    Unanswered: SQL query

    Hi All,

    please find the sample table

    cola colb colc cold
    C1 B1 P1 D1 <-
    C1 B1 P2 D2
    C1 B1 P3 D3
    C1 B1 P4 D1 <-
    C1 B1 P5 D5
    C1 B2 P6 D1
    C2 B3 P7 D1 <-
    C2 B3 P8 D2
    C2 B3 P9 D1 <-

    I am trying to write a query that can give me the output marked with <-
    There is pattern in the column but i am not able to devise anything meaningful.

    please advice

    Cheers!

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Without more information this is the only solution I can think of:
    Code:
    SELECT *
    FROM the_sample_table
    WHERE (cola, colb colc, cold) IN ('C1', 'B1', 'P1', 'D1')
    OR  (cola, colb colc, cold) IN ('C1', 'B1', 'P4', 'D1')
    OR  (cola, colb colc, cold) IN ('C2', 'B3', 'P7', 'D1')
    OR  (cola, colb colc, cold) IN ('C2', 'B3', 'P9', 'D1')

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by subhotech View Post
    There is pattern in the column but i am not able to devise anything meaningful.
    did you mean devise or divulge?

    please tell us the pattern
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2010
    Posts
    64
    no , there are 100o of rows. This wont help me.

  5. #5
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by subhotech View Post
    no , there are 100o of rows. This wont help me.
    If your question is incomplete, the answers aren't likely to be better.

    So you have to explain us what exactly you are trying to achieve.

  6. #6
    Join Date
    Jan 2010
    Posts
    64
    The table has 1000 of rows , i am trying to track those records where the cola = C1
    and colb = B1 and colc = can be any value but cold values should match.
    Hence
    C1 B1 P1 D1 <-
    C1 B1 P4 D1 <-
    hope this helps. please let me know

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Cold values?
    What are those?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by anacedent View Post
    Cold values?
    What are those?
    "column d"

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    The table has 1000 of rows , i am trying to track those records where the cola = C1
    and colb = B1 and colc = can be any value but cold values should match.

    SELECT T1.COLA, T2.COLB, T1.COLC, T2.COLC, T1.COLD
    FROM SAMPLE_TABLE T1, SAMPLE_TABLE T2
    WHERE T1.COLA = 'C1'
    AND T2.COLA = 'C1'
    AND T1.COLB = 'B1'
    AND T2.COLB = 'B1'
    AND T1.COLD = T2.COLD;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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