February 18, 2014 at 4:25 pm
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.mark = 0 and t2.mark = 1
but his gives nothing.
Expected output :
1
2
3
4
5
6
not sure what am i doing wrong. any suggestion please
February 18, 2014 at 4:33 pm
This:
where t1.mark = 0 and t2.mark = 1
Should be like this:
WHERE t1.islabel = 0 and t2.islabel = 1
Is that correct?
February 18, 2014 at 4:45 pm
Why wouldn't you see number 7? It's mark matches another id with the same mark and a different islabel. Can you only use the matching combination once and are you wanting to match the ids in some sort of sequential manner?
February 18, 2014 at 4:47 pm
yes. you are correct. but how can i join those two individual queries to produce the result. could you please help me
February 18, 2014 at 5:00 pm
Maybe something like this:
SELECT MIN(id) id
FROM sample t1
WHERE EXISTS(SELECT 1
FROM sample t2
WHERE t1.islabel != t2.islabel
AND t1.mark = t2.mark)
GROUP BY mark, islabel
ORDER BY id
Do you understand how does it work?
February 18, 2014 at 5:11 pm
Hi Luis,
Great learning today for me. Yes i do understand. you are the genius and really i am very thankful to you because i learned some good and important concepts through you in different posts.I like all your posts and greats tips you are giving to the learners like me and others
Really you are great mentor.
February 18, 2014 at 5:18 pm
You're just too kind :blush:
I'm just someone who loves to help 🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply