Issue with join - SQL Server Forums

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.

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources