CODEWITHSUNDEEP
mysqlsql
kajl16
kajl16

Reputation: 27

How do I show only the rows based on the value in the condition?

How do I show only the people in the department where kurt is in?

SELECT * FROM sample_table 
WHERE (name= 'kurt' OR department IN (select department from sample_table
where name = 'kurt'
group by department 
having count(*) > 1));

From this one:

name department
kurt 2
john 1
mark 1
rose 2
anna 2

to this one:

name department
kurt 2
rose 2
anna 2

Upvotes: 1

Views: 43

Answers (3)

Omari Victor Omosa
Omari Victor Omosa

Reputation: 2879

Answer as below

SELECT
    name, department
FROM
    sample_table
where
    department in (
        select distinct department
    from
        sample_table
    where
        name = 'kurt')

This will return even when kurt is in many departments

Upvotes: 1

Wellerman
Wellerman

Reputation: 856

Try this:

SELECT *
FROM
    sample_table
WHERE
    department = (
        SELECT
            department
        FROM
            sample_table
        WHERE
            name = 'kurt'
        LIMIT 1
    )

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522254

One approach uses exists logic:

SELECT s1.name, s1.department
FROM sample_table s1
WHERE EXISTS (SELECT 1 FROM sample_table s2
              WHERE s2.department = s1.department AND s2.name = 'kurt');

Upvotes: 2

Related Questions