CODEWITHSUNDEEP
sql
TeTe
TeTe

Reputation: 227

select rows that doesn´t have a value in one column, and have the same ID

I have this table: And I would like to get the IDs, that don´t have the language english.

+----+----------+-------+
| ID | LANGUAGE | NAME  |
+----+----------+-------+
| 1  | english  | james |
+----+----------+-------+
| 1  | french   | john  |
+----+----------+-------+
| 2  | french   | ted   |
+----+----------+-------+
| 3  | german   | tom   |
+----+----------+-------+
| 3  | english  | james |
+----+----------+-------+
| 4  | spanish  | lucy  |
+----+----------+-------+
| 4  | german   | Bud   |
+----+----------+-------+

The output should be something like:

+----+----------+-------+
| ID | LANGUAGE | NAME  |
+----+----------+-------+
| 2  | french   | ted   |
+----+----------+-------+
| 4  | spanish  | lucy  |
+----+----------+-------+
| 4  | german   | Bud   |
+----+----------+-------+

Upvotes: 1

Views: 1333

Answers (2)

Deepstop
Deepstop

Reputation: 3807

The subquery builds a list of the id fields of all records where the language is English.

Using that, we find all records in the table which have id fields that are not in that list. So those are the records for ids where there is no English entry.

SELECT * FROM table
    WHERE table.id NOT IN (SELECT id FROM table WHERE language='english');

Upvotes: 1

forpas
forpas

Reputation: 164064

With NOT EXISTS:

select * from tablename t
where not exists (
  select 1 from tablename
  where id = t.id and language = 'english'
)

Upvotes: 2

Related Questions