SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Issue with join
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqllover
Constraint Violating Yak Guru

India
331 Posts

Posted - 02/18/2014 :  18:28:49  Show Profile  Reply with Quote
Hi,

This is my sample data

with sample as (
  
  select 1 as id, 20 as mark,0 as islabel union all
   select 2 as id, 20 as mark,1 as islabel union all
    select 3 as id, 30 as mark,0 as islabel union all
     select 4 as id, 30 as mark,1 as islabel union all
      select 5 as id, 40 as mark,0 as islabel union all
       select 6 as id, 40 as mark,1 as islabel union all
        select 7 as id, 40 as mark,0 as islabel union all
       select 8 as id, 50 as mark,1 as islabel union all
       select 9 as id, 40 as mark,0 as islabel union all
       select 10 as id, 50 as mark,1 as islabel ) 


Individual query:

Select id,mark from sample where islabel = 0

Select id,mark from sample where islabel = 1

i need to join these two select based on mark

i need make self join on this and get the id who's score are equal.

this is my try


SELECT t1.id, t1.mark
  FROM sample t1 
  INNER JOIN sample t2  on (t1.mark = t2.mark) where t1.islabel = 0 and t2.islabel = 1


but his gives nothing.

Expected output :

1
2
3
4
5
6

not sure what am i doing wrong. any suggestion please

Edited by - sqllover on 02/18/2014 18:48:16

nagino
Yak Posting Veteran

Japan
65 Posts

Posted - 02/18/2014 :  19:17:14  Show Profile  Reply with Quote
Why not expected output contains id 7, 9, it seems these are pair of id 6.

If need pair of minimum ids, test following.
SELECT MIN(t1.id) id
FROM sample t1
WHERE EXISTS (
	SELECT *
	FROM sample t2
	WHERE t1.mark = t2.mark
	AND t1.id != t2.id
	AND t1.islabel != t2.islabel)
GROUP BY t1.mark, t1.islabel
ORDER BY MIN(t1.id)


If need all ids, test following.
SELECT t1.id
FROM sample t1
WHERE EXISTS (
	SELECT *
	FROM sample t2
	WHERE t1.mark = t2.mark
	AND t1.id != t2.id
	AND t1.islabel != t2.islabel)
ORDER BY t1.id


-------------------------------------
From Japan
Sorry, my English ability is limited.
Go to Top of Page

sqllover
Constraint Violating Yak Guru

India
331 Posts

Posted - 02/18/2014 :  19:31:48  Show Profile  Reply with Quote
Hi Nagino,

It's great query and thanks for your help. Really Appreciate you.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000