SQLTeam.com | Weblogs | Forums

How to Select value with condition on another column, Please HELP!

tbl_AGE has 4 columns OrgID, EmpID, Name, Salary. I want to display OrgID values whose EmpID values have salary more than 1000. so if any of EmpID does not satisfy condition then OrgID value should not be printed. To display OrgID, all respective EmpID should meet salary criteria.

OrgID   EmpID    Name   Salary 
1          1    Sam    800
1          2    Gel    1200
2          1    Moy    1100
2          2    Gar    1300

Sometimes an example of the results will help us to help you.

I think you need something like this

SELECT _AGEID1.* 
FROM _AGE _AGEID1
INNER JOIN _AGE _AGEID2 
   ON _AGEID1.ID2=_AGEID2.ID1
WHERE _AGEID2.Salary > 1000

It's not working.

In My example there is only one Table and name is table_AGE.
I just want to print ID1 values whose ID2 has salary greater than 1000. If any of ID2 does not satisfy condition then ID1 value should not print.

You can understand problem like There is OrganizationID column and EmployeeID column. For 1 OrgID there may be multiple EmpID so i just want to display those OrgID only whose EmpID meets the salary > 1000. Example : OrgID EmpID Name Salary 1 1 Sam 800 1 2 Gel 1200 2 1 Moy 1100 1 3 Gar 1300 3 1 Man 900 3 2 Joy 2000 So here only below line satisfy condition: 2 1 Moy 1100

What would the final result look like if you can put it as an image as you have it with the sample data.

What does not print mean?

Select * from table_AGE where salary > 1000

No, it's not correct.

To make it more readable, let me change column names.
tbl_AGE has 4 columns OrgID, EmpID, Name, Salary. I want to display OrgID values whose EmpID values have salary more than 1000. so if any of EmpID does not satisfy condition then OrgID value should not be printed. To display OrgID, all respective EmpID should meet salary criteria.

OrgID EmpID Name Salary
1 1 Sam 1100
1 2 Joy 900
2 1 Ram 1200
2 2 Son 1500

So only OrgID 2 should be displayed. because It's both EmpID meets salary criteria.

Let me elaborate more with updated column names.

OrgID EmpID Name Salary
1 1 Sam 1100
1 2 Joy 900
2 1 Ram 1200
2 2 Son 1500

So only OrgID 2 should be displayed. because It's both EmpID meets salary criteria.

What in the world does should not print mean

Every post you are posting to make it clearer is actually making it more confusing. Post sample data using ddl and dml

print mean display i want to display only those OrgID whose all EmpID meets salary criteria of 1000.

Please provide your sample data with proper ddl and dml

My table tbl_AGE is :

|OrgID|EmpID|Name|Salary|
|-----|-----|----|------| 
|1    |1    |Sam |800   |
|1    |2    |Gel |1200  |
|2    |1    |Moy |1100  |
|2    |2    |Gar |1300  |


Output should be 
OrgID
2 

because It's both EmpID meets salary criteria.


https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=93d318363ec5e3feff98e9f9356ab7c2
Select distinct OrgId from table_AGE where salary > 1000

hi

hope this helps ..

here is drop create data script
create table Test_Data
(
OrgID int ,
EmpID int ,
Name varchar(10), 
Salary int 
)

insert into Test_Data select 1 ,  1  ,'Sam', 800
insert into Test_Data select 1 ,  2  ,'Gel', 1200
insert into Test_Data select 2 ,  1  ,'Moy', 1100
insert into Test_Data select 2 ,  2  ,'Gar', 1300
; WITH cte
     AS (SELECT *,
                Sum(CASE
                      WHEN salary > 1000 THEN 0
                      ELSE 1
                    END)
                  OVER(
                    partition BY orgid
                    ORDER BY orgid) AS smcnt
         FROM   test_data)
SELECT *
FROM   cte
WHERE  smcnt = 0 

image

SELECT OrgID
FROM tbl_AGE
GROUP BY OrgID
HAVING MIN(Salary) >= 1000
1 Like