Professional Web Applications Themes

Query problem. - Microsoft SQL / MS SQL Server

column 1 column2 column 3 column 4 1 current 1.0 yr1 1 previous 1.2 yr0 2 current 1.3 yr1 2 previous 2.0 yr0 3 previous 1.4 yr0 4. current 2.0 yr1 4. previous 1.5 yr0 ................................................. I want to use a query to return the following, basically, if there is no "current", then it shouldn't return "previous" . is it possible? column 1 column2 column 3 column 4 1 current 1.0 yr1 1 previous 1.2 yr0 2 current 1.3 yr1 2 previous 2.0 yr0 4. current 2.0 yr1 4. previous 1.5 yr0...

  1. #1

    Default Query problem.

    column 1 column2 column 3 column 4

    1 current 1.0 yr1
    1 previous 1.2 yr0
    2 current 1.3 yr1
    2 previous 2.0 yr0
    3 previous 1.4 yr0
    4. current 2.0 yr1
    4. previous 1.5 yr0
    .................................................


    I want to use a query to return the following, basically, if there is no
    "current", then it shouldn't return "previous" .
    is it possible?


    column 1 column2 column 3 column 4

    1 current 1.0 yr1
    1 previous 1.2 yr0
    2 current 1.3 yr1
    2 previous 2.0 yr0
    4. current 2.0 yr1
    4. previous 1.5 yr0


    Mike Guest

  2. #2

    Default Re: Query problem.

    try this, should work (untested though)

    select a.* from table a inner join
    (select column1 from table where column2 in('current', 'previous')
    group by column1 having count(*) >= 2) b
    on a.column1= b.column1

    --
    -Vishal

    "Mike" <pearl_77hotmail.com> wrote in message
    news:#tTy7tyRDHA.940TK2MSFTNGP11.phx.gbl...
    > column 1 column2 column 3 column 4
    >
    > 1 current 1.0 yr1
    > 1 previous 1.2 yr0
    > 2 current 1.3 yr1
    > 2 previous 2.0 yr0
    > 3 previous 1.4 yr0
    > 4. current 2.0 yr1
    > 4. previous 1.5 yr0
    > ................................................
    >
    >
    > I want to use a query to return the following, basically, if there is no
    > "current", then it shouldn't return "previous" .
    > is it possible?
    >
    >
    > column 1 column2 column 3 column 4
    >
    > 1 current 1.0 yr1
    > 1 previous 1.2 yr0
    > 2 current 1.3 yr1
    > 2 previous 2.0 yr0
    > 4. current 2.0 yr1
    > 4. previous 1.5 yr0
    >
    >

    Vishal Parkar Guest

  3. #3

    Default Re: Query problem.

    You can do this in many ways, using self joins, derived tables or even with
    subqueries like:

    SELECT *
    FROM tbl
    WHERE EXISTS( SELECT *
    FROM tbl t1
    INNER JOIN tbl t2
    ON t1.col1 = t2.col1
    AND t1.col1 = tbl.col1
    WHERE t1.col2 = 'current'
    AND t2.col2 = 'previous' ) ;

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Sen Guest

  4. #4

    Default Re: Query problem.

    okay... but how about this,
    if there is current but no previous, then we should not eliminate
    current.
    is that possible?


    "Anith Sen" <anithbizdatasolutions.com> wrote in message
    news:uUs$U0yRDHA.1624tk2msftngp13.phx.gbl...
    > You can do this in many ways, using self joins, derived tables or even
    with
    > subqueries like:
    >
    > SELECT *
    > FROM tbl
    > WHERE EXISTS( SELECT *
    > FROM tbl t1
    > INNER JOIN tbl t2
    > ON t1.col1 = t2.col1
    > AND t1.col1 = tbl.col1
    > WHERE t1.col2 = 'current'
    > AND t2.col2 = 'previous' ) ;
    >
    > --
    > - Anith
    > ( Please reply to newsgroups only )
    >
    >

    Mike Guest

  5. #5

    Default Re: Query problem.

    Just remove the last condition in the WHERE clause like:

    SELECT *
    FROM tbl
    WHERE EXISTS( SELECT *
    FROM tbl t1
    INNER JOIN tbl t2
    ON t1.col1 = t2.col1
    AND t1.col1 = tbl.col1
    WHERE t1.col2 = 'current' ) ;

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Sen Guest

Similar Threads

  1. Query problem...
    By Martine Duval in forum MySQL
    Replies: 4
    Last Post: June 17th, 04:02 PM
  2. Query of Query problem
    By JakeFlynn in forum Macromedia ColdFusion
    Replies: 18
    Last Post: August 11th, 08:15 AM
  3. Problem with query
    By toby007 in forum Coldfusion Database Access
    Replies: 2
    Last Post: June 9th, 10:52 PM
  4. Query on Query and CF casting problem
    By obxlefty in forum Coldfusion - Advanced Techniques
    Replies: 0
    Last Post: May 18th, 12:44 PM
  5. Problem query
    By Scott Ashby in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 2nd, 07:01 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139