Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Issue with join

Author  Topic 

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-02-18 : 18:28:49
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

nagino
Yak Posting Veteran

75 Posts

Posted - 2014-02-18 : 19:17:14
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

338 Posts

Posted - 2014-02-18 : 19:31:48
Hi Nagino,

It's great query and thanks for your help. Really Appreciate you.
Go to Top of Page
   

- Advertisement -