Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2012
    Posts
    1

    Question Unanswered: select the id if one column has a particular value and not another

    Hi

    I have a table which has id and attributename as columns.
    there can be multiple entries of an id with different attribute names.

    I want to select all those ids which have attribute 'JiraId' but not 'JiraHost'

    Id AttributeName
    1 JiraId
    1 JiraHost
    1 JiraSummary
    1 Location
    2 JiraId
    2 JiraSummary
    2 Location
    3 JiraId
    3 JiraSummary
    4 JiraId
    4 JiraHost
    4 JiraSummary

    In this sample table the result according to my problem should be 2,3 as for these Ids JiraHost attribute is not present.

    Please help

  2. #2
    Join Date
    Jan 2012
    Location
    Lisbon
    Posts
    115
    Provided Answers: 1
    Hello,

    I've called your table A.
    I don't really like this query, but it does what you want. ( needs to be enhanced )


    Code:
    drop table A
    go
    
    create table A (id int, attr varchar(10))
    go
    
    insert into A values (1,'JiraId')
    insert into A values (1,'JiraHost')
    insert into A values (1,'JiraSummary')
    insert into A values (1,'Location')
    insert into A values (2,'JiraId')
    insert into A values (2,'JiraSummary')
    insert into A values (2,'Location')
    insert into A values (3,'JiraId')
    insert into A values (3,'JiraSummary')
    insert into A values (4,'JiraId')
    insert into A values (4,'JiraHost')
    insert into A values (4,'JiraSummary')
    go
    
    
    
    select A0.attr, convert(varchar, A0.id) + ',' + convert(varchar, A1.id) as number  
    from A as A0, A as A1
    where A0.id not in ( select A2.id from A as A2 where A2.attr='JiraHost')
    and A1.id not in ( select A2.id from A as A2 where A2.attr='JiraHost')
    and A0.attr='JiraId'
    and A0.attr = A1.attr
    and A0.id < A1.id

    Hope it helps.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT id
      FROM a
     WHERE attr IN ('JiraId','JiraHost')
    GROUP
        BY id
    HAVING COUNT(CASE WHEN attr='JiraHost' THEN 'oh noes' END)) = 0
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Tags for this Thread

Posting Permissions

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