intersection on a single table

ultraman69 · #1 (**permalink**) 11-12-04, 11:44

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 ?

Ten_Spoons · #2 (**permalink**) 11-12-04, 12:06

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.

r937 · #3 (**permalink**) 11-12-04, 12:08

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 )

ultraman69 · #4 (**permalink**) 11-12-04, 12:37

Both solution works !

Thanks a lot !

SQL_Learner · #5 (**permalink**) 01-02-13, 04:19

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??

r937 · #6 (**permalink**) 01-02-13, 04:52

could you please rephrase your question, right now it doesn't make sense

SQL_Learner · #7 (**permalink**) 01-02-13, 05:09

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..

YesAgile · #8 (**permalink**) 01-08-13, 19:54

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'

Internet Services

Web Development

Digital Marketing

Consumer Tech