Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2004
    Location
    Quebec, Canada
    Posts
    7

    Question intersection on a single table

    Hi all !

    I have a table with no keys (temp table) which looks like this :
    col1|col2|col3
    001|A|.087
    001|B|.032
    001|C|.345
    002|A|.324
    002|B|.724
    003|A|.088
    003|C|.899
    001|A|.087
    001|A|.234
    001|B|.032

    As you see, there is some duplicate entries in it. I would like to get a list of all the rows that have the same col1 and col2 BUT different col3 value. The result should return col1=001 col2=A but NOT col1=001 col2=B. I tried a lot of queries with EXISTS, HAVING, etc... but nothing seems to work.

    Anyone have an idea how I can do it ?

  2. #2
    Join Date
    Aug 2004
    Posts
    54
    How about something like this?

    Code:
    select distinct test.col1, test.col2, test.col3
    from test
    inner join
    	(select col1, col2, count(1) as colcount
    	from test
    	group by col1, col2
    	having count(1) > 1) a on test.col1 = a.col1 and test.col2 = a.col2
    Have some fun.
    Last edited by Ten_Spoons; 11-12-04 at 13:14.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select col1, col2, col3
      from yourtable as t
     where 1   
         < ( select count(distinct col3)
               from yourtable
              where col1 = t.col1
                and col2 = t.col2 )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jul 2004
    Location
    Quebec, Canada
    Posts
    7
    Both solution works !

    Thanks a lot !

  5. #5
    Join Date
    Jan 2013
    Posts
    2
    Hi TEN_SPOONS/r937

    i also have same problem with just a change that i want to delete the rows which have different value for the column 3 in the above query .
    for ex. i want to delete the
    col1|col2|col3
    001|A|.087
    001|B|.032
    001|C|.345
    002|A|.324
    002|B|.724003|A|.088
    003|C|.899
    001|A|.087
    001|A|.234001|B|.032

    only A= .087 and B= .032 should come

    can anybody help??

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    could you please rephrase your question, right now it doesn't make sense
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2013
    Posts
    2
    r937 ,

    i have a data record in V_TABLE table in which values are like this

    Code:
    ID       V_TYPE       STATUS 
    
    2124	H1	APPR
    2124	H1	VISA
    23573	H1	VISA
    23573	H1	APPR
    23869	H1	APPR
    23869	H1	VISA
    5458	L1	VISA
    5458	L1	APPR
    16673	L1	VISA
    16673	L1	APPR
    10074	H1	APPR
    10093	L1	VISA
    10108	L1	VISA

    now i want to delete those emplid(complte rows) who have STATUS = 'APPR' only when they have STATUS = 'APPR' as well for same V_TYPE

    for ex. 2124 emplID has both 'APPR' n 'VISA' status for H1 V_TYPE so only 'APPR' row must be deleted.

    i have applied this code but it is deleting complete table .

    DELETE FROM V_TABLE WHERE EXISTS
    (SELECT * FROM V_TABLE A where
    ID IN
    (SELECT ID FROM V_TABLE B WHERE B.V_STATUS = 'APPR'
    AND B.ID = A.EMPLID
    AND B.V_TYPE = A.V_TYPE
    INTERSECT
    SELECT ID FROM V_TABLE C WHERE V_STATUS = 'VISA'
    AND C.ID = A.ID
    AND C.V_TYPE = A.V_TYPE)
    AND A.VISA_STATUS = 'APPR'
    and A.ID = V_TABLE.ID)

    kindly help..
    Last edited by Pat Phelan; 01-02-13 at 10:52. Reason: Trivial formatting changes for readability

  8. #8
    Join Date
    Jan 2013
    Location
    Woodland Hills, CA
    Posts
    18
    try this,

    delete t
    from dbo.yourTable t
    join
    (
    select v1.* from dbo.yourTable v1
    where
    v1.status = 'visa'
    ) v
    on
    t.id = v.id
    and t.v_type = v.v_type
    where
    t.status = 'appr'

Posting Permissions

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