CODEWITHSUNDEEP
mysql
Helter Skelter
Helter Skelter

Reputation: 45

Mysql select results that have only a specific condition

here is my problem: I have a list of users that are associated with different international projects, I want to get the users that are working exclusively for US projects, so let's say if someone has worked for a German project, I don't want him in the list. It involves three tables: users, projects, company

Here is the first query to get users that are working for US projects: 

SELECT DISTINCT users.forename, users.surname, users.email, users.company_id,  users.user_id
FROM projects, company, users
WHERE projects.project_type = 'US'
AND company.project_id = projects.project_id
AND users.company_id = company.company_id
ORDER BY surname, forename

Now how can I exclude users that have also other countries' projects?

Upvotes: 3

Views: 146

Answers (7)

shola
shola

Reputation: 704

Try this i think you need MINUS:

SELECT 
    users.forename, users.surname, users.email, users.company_id,  users.user_id
FROM 
    users
where 
    user_id not in(SELECT DISTINCT user_id from users where company_id in
    (
        Select company_id from company where project_id = (
            Select project_id from projects where project_type != 'US'
        )
    )

Upvotes: 0

Dipesh Parmar
Dipesh Parmar

Reputation: 27364

You can use NOT IN clause to exclude user that work on other project.

In where condition add below line

WHERE projects.user_id 
  NOT IN (select user_id from projects where projects.project_type != 'US')

Upvotes: 2

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

You can do it like this too

SELECT DISTINCT
  users.forename,
  users.surname,
  users.email,
  users.company_id,
  users.user_id
FROM projects
  INNER JOIN company
    ON company.project_id = projects.project_id
  INNER JOIN users
    ON users.company_id = company.company_id
  INNER JOIN (SELECT *
          FROM projects
          WHERE project_type <> 'US') AS project
    ON project.project_id = projects.project_id
WHERE project.project_id IN NULL
GROUP BY projects.project_id
ORDER BY surname, forename

Upvotes: 0

Vivek Sadh
Vivek Sadh

Reputation: 4268

You can use this:-

SELECT DISTINCT users.forename, users.surname, users.email, users.company_id,  users.user_id
FROM projects, company, users
WHERE users.company_id = company.company_id
AND company.project_id = projects.project_id
AND projects.user_id 
IN (select user_id from projects where projects.project_type = 'US')
ORDER BY surname, forename

Upvotes: 0

Narek
Narek

Reputation: 3823

You can try with HAVING. If user worked only in US then COUNT need to be 1:

SELECT DISTINCT users.forename, COUNT(projects.*) AS c, users.surname, users.email, users.company_id,  users.user_id
FROM projects, company, users
WHERE projects.project_type = 'US'
AND company.project_id = projects.project_id
AND users.company_id = company.company_id
GROUP BY projects.project_type
HAVING c = 1
ORDER BY surname, forename

Maybe this code doesn't work, can't check but you can try in this direction.

Upvotes: 1

Marcassin
Marcassin

Reputation: 1405

You can join your tables and filter with the condition

SELECT DISTINCT users.forename, users.surname, users.email, users.company_id,  users.user_id
FROM users
JOIN company USING (company_id)
JOIN projects USING (project_id)
WHERE projects.project_type = 'US'
ORDER BY surname, forename

this query creates rows with users and information about the company and the project and after filter these rows with the condition WHERE projects.project_type = 'US'

Upvotes: 1

agim
agim

Reputation: 1841

A not exists would do the job

SELECT DISTINCT users.forename, users.surname, users.email, users.company_id,  users.user_id
FROM projects, company, users
WHERE projects.project_type = 'US'
AND company.project_id = projects.project_id
AND users.company_id = company.company_id
AND NOT EXISTS (
    SELECT * 
      FROM projects notUS 
     WHERE notUS.project_type <> 'US' 
       AND notUS.user_id = users.user_id)
ORDER BY surname, forename

Upvotes: 0

Related Questions