1 Replies - 558 Views - Last Post: 31 August 2017 - 09:18 AM Rate Topic: -----

#1 ikhlas06  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 178
  • Joined: 20-May 16

is dave score has atleast one '100' than do not show dave

Posted 31 August 2017 - 09:16 AM

person table
-- key is primary_key
|-----|------|
| key | name |
|-----|------|
| 1   | dave |
| 2   | jhon |
|-----|------|


score table
-- key is frogein_key
|-----|--------|
| Key | score  |
|-----|--------|
| 1   | 100    |
| 1   | 30     |
| 1   | 100    |
| 2   | 20     |
|-----|--------|


is dave score has atleast one '100' than do not show dave

output - this is what I want:
 |------|----|
 | jhon | 20 |
 |------|----|



-- note: i want to use sub query
-- note: i want to use main filter at end of query. not in sub query

--This is what I have so far but it print both names, which is wrong
select name, score 
from personT m
left join 
	(
	Select m.key, s.score as score  
	from personT m
	left join scoreT s.key = m.key
	group by score 
	)sub_test on sub_test.key = m.key 
WHERE score <> '100' 

This post has been edited by ikhlas06: 31 August 2017 - 09:16 AM


Is This A Good Question/Topic? 0
  • +

Replies To: is dave score has atleast one '100' than do not show dave

#2 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 13488
  • View blog
  • Posts: 53,864
  • Joined: 12-June 08

Re: is dave score has atleast one '100' than do not show dave

Posted 31 August 2017 - 09:18 AM

Why not use a 'max' on that subquery/
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1