If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > intersection on a single table

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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 ?
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
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 12:14.
Reply With Quote
  #3 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
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
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Jul 2004
Location: Quebec, Canada
Posts: 7
Both solution works !

Thanks a lot !
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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??
Reply With Quote
  #6 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
could you please rephrase your question, right now it doesn't make sense
__________________
rudy.ca | @rudydotca
Buy my SitePoint book: Simply SQL
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
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 09:52. Reason: Trivial formatting changes for readability
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
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'
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On